Partition Pruning YEAR(’1999-06-21’)  evaluates to  1999 , which is found in partition  d5 . In this case, this means that only partitions  d3 ,  d4 , and  d5  are scanned. The remaining partitions may be safely ignored (and are ignored). 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.) 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: 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; 4707 Important 3. Scan only these two partitions and any partitions that may lie between them . 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) ); 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;