Mysql B+ Tree implementation
data-structures, mysql
Solution
(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.
Problem
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)