Understanding MySQL Partition Pruning: Which Partition Should Be Queried?

MySQL supports partitioned tables since version 5.1, allowing a logical table to be split into multiple independent sub‑tables. Only horizontal (range, list, hash) partitioning is supported, and partition pruning can dramatically reduce the amount of data scanned when the WHERE clause restricts the query to a subset of partitions.

What is partition pruning? When the optimizer can determine from the WHERE conditions which partitions may contain matching rows, it only searches those partitions, skipping the rest. This requires the partition expression to be monotonic and the predicates to involve the partition column compared to constants using operators such as =, <>, <, <=, >, >=, BETWEEN, IN, IS NULL, IS NOT NULL.

Implementation details

The pruning logic lives in the JOIN::optimize phase (specifically optimize_aggregated_query ) and was later moved to Query_block::prepare (see WL#4443) to allow pruning before table locks are acquired. The engine maintains two bitmaps in partition_info :

read_partitions : partitions that need to be read.

lock_partitions : partitions that need to be locked.

These bitmaps are initialized in ha_partition::open() and set explicitly when a query specifies partitions (e.g., SELECT * FROM t1 PARTITION(p0,p1) ).

Pruning workflow

The core function is find_used_partitions , which analyses a SEL_ARG tree describing the range constraints. The optimizer builds a SEL_TREE composed of one or more SEL_ARG nodes, then traverses it to decide which partitions satisfy the constraints. Depending on the query, the optimizer may also use find_used_partitions_imerge and find_used_partitions_imerge_list for index‑merge trees.

Example partition definition

CREATE TABLE t1 (
  a INT,
  b INT
n) PARTITION BY RANGE (F(a)) (
  PARTITION p1 VALUES LESS THAN (L1),
  PARTITION p2 VALUES LESS THAN (L2),
  ...
  PARTITION pn VALUES LESS THAN (Ln)
);

Given a query such as:

SELECT * FROM t1 WHERE (a > 10 AND a <= 20) OR (a > 0 AND a < 5);

the optimizer extracts the partition ranges (,L1), [L1,L2), ... and determines that only partitions covering (0,5) and (10,20] need to be accessed.

Restrictions

Only the comparison operators listed above are considered for pruning, combined with AND/OR.

The partition column must be compared to a constant; comparisons to non‑constant expressions prevent pruning.

Non‑monotonic partition functions, or columns of type GEOMETRY or ENUM, cannot be pruned.

Triggers that modify partition columns (BEFORE INSERT/UPDATE) also disable pruning.

Detailed example

CREATE TABLE test (
  a INT, b INT, c INT
) PARTITION BY RANGE COLUMNS(a,b) (
  PARTITION p0 VALUES LESS THAN (0,0),
  PARTITION p1 VALUES LESS THAN (10,10),
  PARTITION p2 VALUES LESS THAN (20,20),
  PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE)
);

EXPLAIN SELECT * FROM test WHERE (a > 0 AND a < 10 AND b = 0 AND (c = 2 OR c = 1))
  OR (b = 1 AND (a > 20 OR b = 12) AND c = 2);

The pruning steps are:

Range 0 < a < 10 narrows the first‑level partition to p1 .

Equality b = 0 leads to sub‑partition sp1 after evaluating c = 1 , yielding p1sp1 .

Similarly, c = 2 yields p1sp2 .

Condition a > 20 selects first‑level partition p3 , and b = 1 AND c = 2 selects sub‑partition sp3 , giving p3sp3 .

The final set of partitions accessed is p1sp1, p1sp2, p3sp3 .

Conclusion

MySQL partition pruning can greatly improve query performance, but it only works when the query predicates satisfy the above constraints. Avoid non‑monotonic functions, comparisons to non‑constants, unsupported column types, and triggers that modify partition columns to ensure pruning is applied.