Wednesday, May 31, 2006

EXPLAIN to understand partition pruning

As part of the partitioning development in MySQL 5.1 we've added the ability to
check which partitions of a table that is actually accessed in a particular query.
As partitions in a sense can be a sort of index this is an important feature to
help understand performance impact of a query.

The method to use this feature is the normal EXPLAIN command with an
added keyword PARTITIONS. So e.g.
EXPLAIN PARTITIONS select * from t1;

So a slightly more useful example would be
CREATE TABLE t1 (a int)
PARTITION BY RANGE (a)
(PARTITION p0 VALUES LESS THAN (10),
PARTITION p1 VALUES LESS THAN (20),
PARTITION p2 VALUES LESS THAN (30));

Now if we do an equal query we should only need to access one partition:
This will be verified by the command:
EXPLAIN PARTITIONS select * from t1 WHERE a = 1;
/* Result in p0 being displayed in the list of partitions */

A range query will also be pruned nicely in this case (a is a function that is
increasing and thus range optimisations can be performed, YEAR(date) and
FUNC_TO_DAYS(date) are two other functions that are known to be
monotonically increasing.

EXPLAIN PARTITIONS select * from t1 WHERE a <= 1 AND a>= 12;
/* Result in the range being mapped to p0, p1 */

LIST partitions will be pruned in the same cases as RANGE for range-pruning
of partitions.

HASH partitioning has no natural concept for ranges since different values
map more or less randomly into partitions. We do however apply an
optimisation for short ranges such that the following will happen.

CREATE TABLE t1 (a int)
PARTITION BY HASH (a)
PARTITIONS 10;

EXPLAIN PARTITIONS select * from t1 WHERE a < 3 AND a > 0;
In this case the range consists of only two values 1 and 2. Thus we simply map
the interval to a = 1 OR a = 2 and here we get p1 and p2 as the partitions to
use.

2 comments:

pabloj said...

Thanks for the detailed tutorial, I'd like to add that IMHO there is no point in having two "explains", see feature request http://bugs.mysql.com/bug.php?id=20163

Anonymous said...

Hi Mikael,
What is the algorithm of partitioning by KEY? I found it's not the same as method 'HASH' or 'LINEAR HASH', and there isn't much detailed document about this algorithm.