Records: 10 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT * FROM trb1 WHERE id < 5\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: trb1 partitions: p0,p1 type: range possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 7 Extra: Using where 

If EXPLAIN  is used to examine a query against a nonpartitioned table, no error is produced, but the value of the partitions  column is always NULL . 

The rows  column of EXPLAIN  output displays the total number of rows in the table. 

## 24.4 Partition Pruning 

See also Section 13.8.2, “EXPLAIN Statement” . 

The optimization known as partition pruning  is based on a relatively simple concept which can be described as “Do not scan partitions where there can be no matching values” . Suppose a partitioned table t1  is created by this statement: 

CREATE TABLE t1 ( fname VARCHAR(50) NOT NULL, lname VARCHAR(50) NOT NULL, region_code TINYINT UNSIGNED NOT NULL, dob DATE NOT NULL ) PARTITION BY RANGE( region_code ) ( PARTITION p0 VALUES LESS THAN (64), PARTITION p1 VALUES LESS THAN (128), PARTITION p2 VALUES LESS THAN (192), PARTITION p3 VALUES LESS THAN MAXVALUE ); 

Suppose that you wish to obtain results from a SELECT  statement such as this one: 

SELECT fname, lname, region_code, dob FROM t1 WHERE region_code > 125 AND region_code < 130; 

It is easy to see that none of the rows which ought to be returned are in either of the partitions p0  or p3 ; that is, we need search only in partitions p1  and p2  to find matching rows. By limiting the search, it is possible to expend much less time and effort in finding matching rows than by scanning all partitions in the table. This “cutting away”  of unneeded partitions is known as pruning . When the optimizer can make use of partition pruning in performing this query, execution of the query can be an order of magnitude faster than the same query against a nonpartitioned table containing the same column definitions and data. 

The optimizer can perform pruning whenever a WHERE  condition can be reduced to either one of the following two cases: 

• partition_column  = constant 

• partition_column  IN ( constant1 , constant2 , ..., constantN ) 

