The most disappointing sentence in MySQL operations is "we partitioned it and nothing got faster." I have heard it from teams who split a two-billion-row events table into monthly RANGE partitions, shipped the change, and watched dashboard latency stay exactly where it was. The partitioning usually was not wrong. The queries were written in a way that made pruning impossible, so every statement still opened and scanned all 48 partitions.
Pruning is the whole game for query performance on partitioned tables. When it works, a query touches one or two partitions and the other 46 might as well not exist. When it fails, you carry all the operational cost of partitioning and get none of the speed. This is how I diagnose it on MySQL 8.0 and 8.4.
What pruning actually is
For RANGE and LIST partitioning, the optimizer examines the WHERE clause at plan time, compares the predicates against the partitioning expression, and eliminates partitions that cannot contain matching rows. This happens before execution, so a pruned partition is never even opened: no index dive, no pages read, no locks taken. HASH and KEY partitioning do prune on equality and IN predicates — the optimizer maps the value straight to one partition, and it can even convert a short integer range into an IN list when the range is smaller than the partition count — but a wide range scatters across every partition by design, so everything below concerns RANGE and LIST, which is what people use for time-series retention anyway.
Two properties worth internalizing. Pruning is decided from the partitioning expression and your predicate, not from indexes. And pruning is all-or-nothing per partition: a predicate that covers half of a partition's range keeps that whole partition in the plan, though an index can still do range access within it.
Reading the partitions column
The diagnosis tool is plain EXPLAIN: on MySQL 8.0 and 8.4 its output includes a partitions column listing exactly which partitions the plan will touch. The old EXPLAIN PARTITIONS variant was removed in 8.0 — for a partitioned table the column shows up without any extra keyword, so there is nothing extra to type:
EXPLAIN
SELECT COUNT(*)
FROM events
WHERE occurred_at >= '2026-06-01'
AND occurred_at < '2026-07-01';If the partitions column says p2026_06, pruning worked. If it lists p2023_01 through p2026_07, it did not, and the rest of this post is about why.
Prune-killer number one: functions on the partition key
The single most common cause is wrapping the partitioning column in a function inside the WHERE clause. DATE(occurred_at) = '2026-06-15', YEAR(occurred_at) = 2026, MONTH(occurred_at) BETWEEN 5 AND 6 — all of these force MySQL to evaluate the function per row, which means the predicate can no longer be mapped onto partition boundaries at plan time. The pruner reasons about the partitioning expression; once the column sits inside a function call in your predicate, that reasoning is gone. There is a narrow exception worth knowing: the partitioning expression itself may use a small set of functions, TO_DAYS, TO_SECONDS, YEAR, and UNIX_TIMESTAMP among them, and pruning still works when your predicates are plain ranges on the underlying column. But functions wrapped around the column in your WHERE clause remain poison.
The fix is almost always to rewrite the predicate as a bare-column range. DATE(occurred_at) = '2026-06-15' becomes occurred_at >= '2026-06-15' AND occurred_at < '2026-06-16'. Half-open ranges like that prune cleanly, they can also use an index on the column, and they sidestep the whole class of bug.
Prune-killer number two: type and collation mismatches
The quieter cause is a comparison the optimizer cannot safely map to partition boundaries. Comparing a DATETIME column to a bare integer, comparing an integer partition key to a quoted string that needs conversion, or comparing across collations can each force an implicit conversion on the column side of the comparison — and a converted column is, to the pruner, the same as a wrapped one. The same family of issue bites joins between mismatched charsets, which I wrote about in the charset and collation mismatch post. The rule of thumb: compare the partition key to a constant of the same type, or to something the optimizer can fold into one.
Non-deterministic expressions are the third member of the family. A predicate like occurred_at > NOW() - INTERVAL 30 DAY often does prune on modern MySQL, because the optimizer can constant-fold it at plan time, but anything dependent on per-row values, user variables, or non-deterministic functions will not. When in doubt, compute the boundary in the application and send a literal.
Partitioning on the bare column: RANGE COLUMNS
Half of these headaches vanish if you partition on the column itself instead of an expression. RANGE COLUMNS partitioning lets you define boundaries directly on a DATE or DATETIME column, in the form PARTITION p2026_06 VALUES LESS THAN ('2026-07-01'), with no function in the partitioning expression at all. Then any plain range predicate on the column prunes naturally. If you are still carrying an old PARTITION BY RANGE (MONTH(occurred_at)) schema, that shape prunes only for predicates that match the expression's semantics, and it confuses everyone who reads the DDL. The schema-design side of that decision is in the MySQL partitioning operational guide.
When you must force it: explicit partition selection
MySQL also lets you name partitions directly, as in SELECT ... FROM events PARTITION (p2026_06). This is not pruning — it is you doing the pruner's job — and it is the right tool in two places: maintenance jobs that intentionally walk one partition at a time, and queries where the application already knows the target partition and you want to eliminate all doubt. INSERT, UPDATE, and DELETE prune too, by the way. A single-row UPDATE whose WHERE clause includes the partition key touches one partition; the same UPDATE without the partition key must check every partition that could hold the row, which on a 48-partition table turns a point update into 48 index lookups.
The other failure: too many partitions
Even with perfect pruning, partition counts in the thousands carry real costs. Every partition is its own InnoDB tablespace with its own indexes, DDL and table-open operations scale with the count, and any statement that cannot prune pays per-partition overhead. I keep retention windows coarse enough that the count stays in the low hundreds at most, and I enforce retention with fast DROP PARTITION operations instead of bulk deletes — metadata surgery instead of a billion-row DELETE. The purge and undo implications of the delete-based alternative are covered in the history list length post.
Catching the rows-examined regression
Pruning failures announce themselves quietly, as rows-examined regressions on statements nobody touched. Watching for exactly that is a core part of the MySQL support MonPG is building: statement-level rows examined trended over time, so "this query started reading 400 million rows after the app release" surfaces before the help desk does. The scope caveat matters: MonPG monitors PostgreSQL today, and the MySQL side is coming soon and being built now. Until it ships, plain EXPLAIN plus the slow query log will find every pruning failure you have — the workflow is in the slow query log digest post — and Postgres fleets can run the finished version of that loop at MonPG for PostgreSQL. More of the series is on the blog.