Mysql uses B+ tree for implementing indexes. Lets say my primary index is of type (name, age, city). Can someone throw some light how Mysql implements a B+ tree on these kinds of keys ?
My hunch is that it treats the whole tuple as a key and it treats it like a partial order. Eg:
(John, 24, New Jersey) < (John, 24, New York) < (John, 25, New Jersey) < (John, 25, New York)
(John, 24, New Jersey) < (John, 24, New York) < (John, 25, New Jersey) < (John, 25, New York)
Yes, that's why it is important in which order you lay out columns in a key.
The key won't be of any help if you do not look for values that can be satisfied from looking at the start of it - e.g. when you filter for age and/or city, but not for name, the key can't be used. On the other hand, if you'd filter for name only, the key would be used.