content_image/681028.jpg
SELECT * FROM t4 WHERE region_code BETWEEN 3 AND 5; 

In both these cases, the WHERE  clause is transformed by the optimizer into WHERE region_code IN (3, 4, 5) . 

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

## Important 

This optimization is used only if the range size is smaller than the number of partitions. Consider this statement: 

DELETE FROM t4 WHERE region_code BETWEEN 4 AND 12; 

The range in the WHERE  clause covers 9 values (4, 5, 6, 7, 8, 9, 10, 11, 12), but t4 has only 8 partitions. This means that the DELETE  cannot be pruned. 

When a table is partitioned by HASH  or [LINEAR] KEY , pruning can be used only on integer columns. For example, this statement cannot use pruning because dob  is a DATE  column: 

SELECT * FROM t4 WHERE dob >= ’2001-04-14’ AND dob <= ’2005-10-15’; 

However, if the table stores year values in an INT  column, then a query having WHERE year_col >= 2001 AND year_col <= 2005  can be pruned. 

Tables using a storage engine that provides automatic partitioning, such as the NDB  storage engine used by MySQL Cluster can be pruned if they are explicitly partitioned. 

## 24.5 Partition Selection 

Explicit selection of partitions and subpartitions for rows matching a given WHERE  condition is supported. Partition selection is similar to partition pruning, in that only specific partitions are checked for matches, but differs in two key respects: 

1. The partitions to be checked are specified by the issuer of the statement, unlike partition pruning, which is automatic. 

2. Whereas partition pruning applies only to queries, explicit selection of partitions is supported for both queries and a number of DML statements. 

SQL statements supporting explicit partition selection are listed here: 

• SELECT 

• DELETE 

• INSERT 

• REPLACE 

• UPDATE 

• LOAD DATA . 

• LOAD XML . 

The remainder of this section discusses explicit partition selection as it applies generally to the statements just listed, and provides some examples. 

Explicit partition selection is implemented using a PARTITION  option. For all supported statements, this option uses the syntax shown here: 

