Re: MySQL Partitioning showing low performance

[Rick James]

> since ideally, searching in a specific partition for a record (with index)

Unfortunately, MySQL opens all partitions, even when it does not need to.

For that, and other reasons, PARTITIONing often hurts performance a little. Sometimes (not for your query) PARTITIONs can significantly help performance.

Let's dissect this further...
SELECT column8, column9 FROM myTable WHERE column2 = ? AND column3 = ? AND column4 =? AND column5 = ? AND column7 = ? AND column6 = ? LIMIT 1;

When not partitioned, it goes straight to
KEY `index1` (`column2`,`column3`,`column4`,`column5`,`column7`,`column6`),
to find the row(s) needed. This is a reasonably efficient BTree lookup.

When partitioned, it first picks the partition based on column6, then does a similar BTree lookup. This BTree will be smaller than the non-partitioned equivalent, but not enough smaller to make much difference.

Each BTree will be 4-5 levels deep. (Rule of Thumb: fan out of 100 per level of BTree.) Perhaps the partitioned case will be 1 level shallower.