CONCEPT Cited by 1 source

Partition pruning¶

Partition pruning is the query-optimizer technique whereby the planner uses a query's WHERE predicates, combined with a partitioned table's partitioning function metadata, to determine which partitions could possibly contain matching rows — and scans only those partitions, skipping the rest. It is the single largest first-order benefit of MySQL partitioning for read-heavy workloads.

Canonical example¶

Morrison's worked example on a library_books table RANGE-partitioned on publication_year:

-- Table:
PARTITION BY RANGE (publication_year) (
    PARTITION p0 VALUES LESS THAN (2001),
    PARTITION p1 VALUES LESS THAN (2011),
    PARTITION p2 VALUES LESS THAN (2021),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

-- Query:
SELECT * FROM library_books WHERE publication_year = 2007;

"If you wanted to query all books published in 2007, MySQL would only need to scan p1 and not the entire table to find the requested data." (Source: sources/2026-04-21-planetscale-what-is-mysql-partitioning)

The planner knows 2007 ∈ [2001, 2011), which is p1's range. Partitions p0, p2, p3 are pruned; only p1's file on disk is opened and scanned.

When pruning kicks in¶

Pruning requires the planner to map predicates into partition-set membership:

Strategy	Prunes on	Doesn't prune on
RANGE (concepts/range-partitioning)	`WHERE k = v`, `WHERE k < v`, `WHERE k BETWEEN a AND b`, `WHERE k IN (v1, v2)`	`WHERE f(k) = v` (function obfuscation)
LIST (concepts/list-partitioning)	`WHERE k = v`, `WHERE k IN (v1, v2)`	Range predicates (set membership has no order)
KEY (concepts/key-partitioning)	`WHERE k = v` (hash determines the partition)	Range predicates (hash smears)

When pruning fails¶

Predicates on non-partition columns — WHERE author = 'Jane Austen' on the RANGE-on-publication_year example can't prune; must scan all partitions.
Function obfuscation of the partition key — WHERE YEAR(publication_year) = 2007 wraps the indexed column and prevents pruning, even though the rewrite WHERE publication_year = 2007 would prune cleanly. Same failure mode as index obfuscation at the index-lookup level.
Implicit type casts — WHERE publication_year = '2007' with the column declared INT may or may not prune, depending on MySQL version and cast rules.
Subqueries and parameterised plans — if the partition key is only known at execute time, the planner may pessimistically scan all partitions.

Relation to indexing¶

Partition pruning is upstream of index lookup:

Pruning narrows the set of partitions to open (disk-level cardinality reduction).
Within each surviving partition, indexes narrow the set of rows to read (row-level cardinality reduction).

The two compose. On a partitioned table with a secondary index on author, the query WHERE publication_year = 2007 AND author = 'Jane Austen' first prunes to p1, then uses p1's author index. The scan is O(partitions_surviving × rows_per_partition_matching_index) instead of O(all_partitions × all_rows).

Verifying pruning with `EXPLAIN`¶

MySQL's EXPLAIN PARTITIONS (or just EXPLAIN in 8.0+, where the partitions column is always shown) reveals which partitions the planner selected:

EXPLAIN SELECT * FROM library_books WHERE publication_year = 2007;
-- partitions column: "p1"  ← pruned to one partition
EXPLAIN SELECT * FROM library_books WHERE author = 'Jane Austen';
-- partitions column: "p0,p1,p2,p3"  ← no pruning, scanning all

Morrison's post asserts pruning happens but doesn't show EXPLAIN output; readers diagnosing pruning in production should lean on EXPLAIN PARTITIONS as the ground truth.

Distinction from shard-level routing¶

Partition pruning is the same-server cousin of Vitess's keyspace-id routing at the sharding altitude: both map WHERE predicates to a subset of physical data units and scan only those. At the partition level the subset is files on one server; at the shard level it is separate servers.

Seen in¶

sources/2026-04-21-planetscale-what-is-mysql-partitioning — Brian Morrison II (PlanetScale, 2023-10-10) canonicalises the "only scan p1 for 2007" pruning demo as the first-order benefit of partitioning. The post doesn't walk EXPLAIN PARTITIONS or enumerate which predicate shapes prune on which strategy — wiki page fills in those axes.