CODEWITHSUNDEEP
mysqldata-structures
Pigol
Pigol

Reputation: 1261

Mysql B+ Tree implementation

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)

Upvotes: 5

Views: 2205

Answers (1)

Tomalak
Tomalak

Reputation: 338148

(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.

Upvotes: 5

Related Questions