MySQL Partitioning: RANGE, LIST, HASH and Pruning

Partitioning splits one logical table into several physical parts (partitions), by a rule on one column. To your queries it is still one table. InnoDB supports it.

It is not primarily a performance feature. Its main benefits are:

Fast removal of old data: dropping a partition is instant, while deleting millions of rows is slow (Changing Millions of Rows).
Partition pruning: a query that filters on the partition column reads only the matching partitions.
Easier maintenance of very large tables (rebuild one partition at a time).

RANGE partitioning by date

The classic use: a log or events table partitioned by month or year.

query.sql

SQL

CREATE TABLE events (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    happened_at DATETIME NOT NULL,
    event_type VARCHAR(20) NOT NULL,
    payload VARCHAR(50) NOT NULL,
    PRIMARY KEY (id, happened_at)
)
PARTITION BY RANGE COLUMNS (happened_at) (
    PARTITION p2026_05 VALUES LESS THAN ('2026-06-01'),
    PARTITION p2026_06 VALUES LESS THAN ('2026-07-01'),
    PARTITION p2026_07 VALUES LESS THAN ('2026-08-01'),
    PARTITION p_future VALUES LESS THAN (MAXVALUE)
);

INSERT INTO events (happened_at, event_type, payload) VALUES
    ('2026-05-10 10:00:00', 'login', 'a'), ('2026-05-20 11:00:00', 'click', 'b'),
    ('2026-06-05 09:00:00', 'login', 'c'), ('2026-06-25 18:30:00', 'click', 'd'),
    ('2026-07-02 08:00:00', 'login', 'e'), ('2026-09-01 12:00:00', 'login', 'f');

Notice that the partitioning column must be part of every unique key, including the primary key. That is why the primary key is (id, happened_at).

query.sql

SQL

SELECT partition_name, table_rows
FROM information_schema.partitions
WHERE table_schema = 'learn' AND table_name = 'events'
ORDER BY partition_ordinal_position;

Output

OUTPUT

+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p2026_05       |          2 |
| p2026_06       |          2 |
| p2026_07       |          1 |
| p_future       |          1 |
+----------------+------------+
4 rows in set

Partition pruning

When the WHERE clause restricts the partition column, MySQL reads only the partitions that can contain matches. EXPLAIN shows them in the partitions column:

query.sql

SQL

1
2

EXPLAIN FORMAT=TRADITIONAL SELECT id FROM events WHERE happened_at >= '2026-06-01' AND happened_at < '2026-07-01';
EXPLAIN FORMAT=TRADITIONAL SELECT id FROM events WHERE event_type = 'login';

Output

OUTPUT

+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | events | p2026_06   | index | PRIMARY       | PRIMARY | 13      | NULL |    2 |    50.00 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set

+----+-------------+--------+-------------------------------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions                          | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+-------------------------------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | events | p2026_05,p2026_06,p2026_07,p_future | ALL  | NULL          | NULL | NULL    | NULL |    6 |    16.67 | Using where |
+----+-------------+--------+-------------------------------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set

The first query reads only partition p2026_06. The second does not restrict happened_at, so every partition is read. Pruning only works if the partitioning column is in the condition, in a form MySQL can evaluate (a plain range, not wrapped in a function).

Dropping old data instantly

query.sql

SQL

1
2

ALTER TABLE events DROP PARTITION p2026_05;
SELECT COUNT(*) AS rows_left FROM events;

Output

OUTPUT

+-----------+
| rows_left |
+-----------+
|         4 |
+-----------+
1 row in set

The two May rows are gone, with no row-by-row delete: only a file is removed. Archive-and-drop jobs for time-series tables use this (monthly or daily partitions).

Add the next partitions ahead of time

query.sql

SQL

ALTER TABLE events REORGANIZE PARTITION p_future INTO (
    PARTITION p2026_08 VALUES LESS THAN ('2026-09-01'),
    PARTITION p2026_09 VALUES LESS THAN ('2026-10-01'),
    PARTITION p_future VALUES LESS THAN (MAXVALUE)
);
SELECT partition_name FROM information_schema.partitions WHERE table_schema = 'learn' AND table_name = 'events' ORDER BY partition_ordinal_position;

Output

OUTPUT

+----------------+
| PARTITION_NAME |
+----------------+
| p2026_06       |
| p2026_07       |
| p2026_08       |
| p2026_09       |
| p_future       |
+----------------+
5 rows in set

A scheduled job (a MySQL event, or cron) should create next month’s partition before it is needed, by splitting the MAXVALUE partition.

Other kinds

Type	Rule	Use
`RANGE` / `RANGE COLUMNS`	Ranges of a value (dates, ids)	Time-based data
`LIST` / `LIST COLUMNS`	A list of values per partition	Regions, categories
`HASH`	Hash of an expression into N partitions	Spread rows evenly
`KEY`	Like `HASH`, with MySQL’s own function	Spread rows by a column

query.sql

SQL

CREATE TABLE regional_sales (id INT NOT NULL AUTO_INCREMENT, region VARCHAR(5) NOT NULL, amount INT NOT NULL, PRIMARY KEY (id, region))
PARTITION BY LIST COLUMNS (region) (
    PARTITION p_north VALUES IN ('N', 'NE'),
    PARTITION p_south VALUES IN ('S', 'SE'),
    PARTITION p_other VALUES IN ('W', 'E')
);
INSERT INTO regional_sales (region, amount) VALUES ('N', 10), ('S', 20);
SELECT partition_name, table_rows FROM information_schema.partitions WHERE table_schema = 'learn' AND table_name = 'regional_sales' ORDER BY partition_ordinal_position;

Output

OUTPUT

+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p_north        |          1 |
| p_south        |          1 |
| p_other        |          0 |
+----------------+------------+
3 rows in set

Limits and surprises

Every unique key must include the partitioning column. This is the biggest limitation, and it often rules partitioning out for tables with a unique business key.
Foreign keys are not supported on partitioned InnoDB tables (neither to nor from them).
No full-text indexes on partitioned tables.
A query without the partition column in the WHERE touches all partitions, which can be slower than an unpartitioned table (each partition is searched separately).
There is a limit of 8192 partitions per table. In practice, keep it to a few hundred.
Partitioning does not replace indexes. A good index is almost always a better performance tool.
NULL values go to the first partition in RANGE.

When to use it

Time-series, log and event tables that grow forever and are purged by age.
Very large tables where maintenance (backup, rebuild, purge) is the problem, not query speed.

When not to

To “make queries faster” on a normal OLTP table: try the right index first.
On tables with foreign keys, or with unique keys that do not contain the partition column.
On small tables.

lightbulb

The retention test

If you can describe the table as “keep the last N months, delete the rest”, partitioning by month fits. If you cannot, you probably want an index instead.