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.

Information Schemas for Partitions

As part of the work in developing partitioning support for 5.1 a new
information schema table has been added. This table can be used to
retrieve information about properties of individual partitions.

To query this table you can issue a query like:
SELECT * FROM information_schema.partitions WHERE
table_schema = "database_name" AND table_name = "name_of_table";

The result of this particular query will be one record per partition in
the table with info about the properties of these partitions.

A query on a non-partitioned table will produce a similar output
although most fields will be NULL. The information_schema.partitions
table is not yet implemented for MySQL Cluster so for MySQL Cluster
tables the output will be all NULLs on the partition specific information.

Below follows a short description of the fields in this information
schema table:
1) TABLE_CATALOG: this field is always NULL
2) TABLE_SCHEMA: This field contains the database name of the table
3) TABLE_NAME: Table name
4) PARTITION_NAME: Name of the partition
5) SUBPARTITION_NAME: Name of subpartition if one exists otherwise
NULL
6) PARTITION_ORDINAL_POSITION: All partitions are ordered in the
same order as they were defined, this order can change as management
of partitions add, drop and reorganize partitions. This number is the
current order with number 1 as the number of the first partition
7) SUBPARTITION_ORDINAL_POSITION: Order of subpartitions within a
partition, starts at 1
8) PARTITION_METHOD: Any of the partitioning variants: RANGE, LIST,
HASH, LINEAR HASH, KEY, LINEAR KEY
9) SUBPARTITION_METHOD: Any of the subpartitioning variants: HASH,
LINEAR HASH, KEY, LINEAR KEY
10) PARTITION_EXPRESSION: This is the expression for the partition
function as expressed when creating partitioning on the table through
CREATE TABLE or ALTER TABLE.
11) SUBPARTITION_EXPRESSION: Same for the subpartition function
12) PARTITION_DESCRIPTION: This is used for RANGE and LIST partitions:
RANGE: Contains the value defined in VALUES LESS THAN. This is an
integer value, so if the CREATE TABLE contained a constant expression
this contains the evaluated expression, thus an integer value
LIST: The values defined in VALUES IN. This is a comma-separated list of
integer values.
13) TABLE_ROWS: Although its name indicates that it is the number of
rows in the table, it is actually the number of rows in the partition.
14) AVG_ROW_LENGTH, DATA_LENGTH, MAX_DATA_LENGTH,
INDEX_LENGTH, DATA_FREE, CREATE_TIME, UPDATE_TIME, CHECK_TIME,
CHECKSUM:
All these fields are to be interpreted in the same as for a normal table
except that the value is the value for the partition and not for the table.
23) PARTITION_COMMENT: Comment on the partition
24) NODEGROUP: This is the nodegroup of the partition. This is only
relevant for MySQL Cluster.
25) TABLESPACE_NAME: This is the tablespace name of the partition.
This is currently not relevant for any storage engine.