content_image/730919.jpg
YEAR(’1999-06-21’)  evaluates to 1999 , which is found in partition d5 . 

3. Scan only these two partitions and any partitions that may lie between them . 

In this case, this means that only partitions d3 , d4 , and d5  are scanned. The remaining partitions may be safely ignored (and are ignored). 

<img src='content_image/730919.jpg'>

## Important 

Invalid DATE  and DATETIME  values referenced in the WHERE  condition of a statement against a partitioned table are treated as NULL . This means that a query such as SELECT * FROM partitioned_table  WHERE date_column  < ’2008-12-00’  does not return any values (see Bug #40972). 

So far, we have looked only at examples using RANGE  partitioning, but pruning can be applied with other partitioning types as well. 

Consider a table that is partitioned by LIST , where the partitioning expression is increasing or decreasing, such as the table t3  shown here. (In this example, we assume for the sake of brevity that the region_code  column is limited to values between 1 and 10 inclusive.) 

CREATE TABLE t3 ( fname VARCHAR(50) NOT NULL, lname VARCHAR(50) NOT NULL, region_code TINYINT UNSIGNED NOT NULL, dob DATE NOT NULL ) PARTITION BY LIST(region_code) ( PARTITION r0 VALUES IN (1, 3), PARTITION r1 VALUES IN (2, 5, 8), PARTITION r2 VALUES IN (4, 9), PARTITION r3 VALUES IN (6, 7, 10) ); 

For a statement such as SELECT * FROM t3 WHERE region_code BETWEEN 1 AND 3 , the optimizer determines in which partitions the values 1, 2, and 3 are found ( r0  and r1 ) and skips the remaining ones ( r2  and r3 ). 

For tables that are partitioned by HASH  or [LINEAR] KEY , partition pruning is also possible in cases in which the WHERE  clause uses a simple =  relation against a column used in the partitioning expression. Consider a table created like this: 

CREATE TABLE t4 ( fname VARCHAR(50) NOT NULL, lname VARCHAR(50) NOT NULL, region_code TINYINT UNSIGNED NOT NULL, dob DATE NOT NULL ) PARTITION BY KEY(region_code) PARTITIONS 8; 

A statement that compares a column value with a constant can be pruned: 

UPDATE t4 WHERE region_code = 7; 

Pruning can also be employed for short ranges, because the optimizer can turn such conditions into IN relations. For example, using the same table t4  as defined previously, queries such as these can be pruned: 

SELECT * FROM t4 WHERE region_code > 2 AND region_code < 6; 

