If you met one of the (few) MySQL consultants around the globe
and asked him/her to review your queries and/or schemas, I am
sure that he/she would tell you something regarding the
importance of good primary key(s) design. Especially in the case
of InnoDB, I’m sure they started to explain to you about index
merges and page splits. These two notions are closely related to
performance, and you should take this relationship into
consideration when designing any index (not just PKs).
That may sound like mumbo jumbo to you, and you may be right. This is not easy stuff, especially when talking about internals. This is not something you deal with on a regular basis, and often you don’t want to deal with it at all.
But sometimes it’s a necessity. If so, this article is for you.
In this article, I want to shed some light in explaining some of the most unclear, behind the scenes operations in InnoDB: page index …
[Read more]