Skip to content

Partition Key is NOT Necessarily the First Key of Composite Indexes

If you do believe that the arrangement of positions in a composite index really matters on the whole performance, here is another evidence for that.

MySQL forces you to add the partition key into every indexes of the table before doing the actual partitioning, but the position of the partition key is not necessarily the first place of the composite indexes. If you always put it in the first place of these indexes, the performance of queries on this table could be compromised.

A better practice is to append the partition key to the original composite index as a starting point for tuning. In my case, I mistakenly added the partition key in the first place of composite indexes, and made a query consume hundreds of second to finish, which used to be finished in less one second.

Leave a Reply

Your email address will not be published. Required fields are marked *