MySQL Forums
Forum List  »  Newbie

Re: MySQL Partitioning showing low performance
Posted by: Rick James
Date: February 09, 2013 12:06AM

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

Options: ReplyQuote

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.