MySQL ignoring subpartition in query

2
votes

I have a table that's partitioned by range on id, and by hash on code (both are integers). 30 partitions, 4 hashed subpartitions apiece, 120 total.

If I do a select on id alone, explain plan shows that it's correctly pruning down to only the partition & subpartitions related to it (4)

If I do a select on id + code, explain plan shows that it's correctly pruning down to the one specific subpartition related to it (1)

HOWEVER...

If I do a select on code alone, explain plan seems to be showing that MySQL is doing a full table scan (120 partitions), instead of acting like Oracle and scanning only the one subpartition of each partition that would be relevant (30 total).

Do I have to do something special to get MySQL to take advantage of subpartition-pruning when it can't prune away entire partitions? Or does MySQL (5.1, at least) just not support taking advantage of subpartitions by themselves?

1
mysqlpartitioning
What engine-type are your using? - Johan
very interesting. I was confused because innoDB does not do hash-keys, but I guess you meant a calculated hash stored in a B+tree. - Johan

1 Answers

0
votes

I found the answer. It appears that only MySQL 5.5 and newer is able to take advantage of subpartitions independently of the main partitioning value. One of these days, I'll remember to start paying attention to which version of the MySQL docs I'm reading.

Final example:

Suppose 'table' is partitioned by range on A and subpartitioned by hash on B.

Query 1: "SELECT * from table where A=? and B=? and C=?":

  • Will work as expected under MySQL 5.1 and newer. MySQL will ignore any partition associated with a range not inclusive of A's value, and will ignore any subpartition not correlated with B's value. Worst-case, it does a brute-force search of only the rows in a single subpartition of a single partition.

Query 2: "SELECT A from table where B=? and C=?":

  • Might work as expected under MySQL 5.5 and newer. If it were Oracle working properly, in the worst case, it would search a single subpartition out of each partition range. If you had 20 partitioned ranges and 4 subpartitions apiece, at worst it would do brute-force searches through 20 subpartitions.

  • Won't work as expected under MySQL 5.1. It will iterate through all 80 subpartitions one by one, and basically do a full table scan.