MySQL Partitioning: RANGE, LIST and HASH With Real Examples

Partitioning splits one large table into several physical pieces, while queries still see a single table. Done for the right reasons, it makes deleting old data instant and speeds up queries that only touch recent rows. Done for the wrong reasons, it adds complexity without helping. Here’s how MySQL partitioning works, and when to use it.

When partitioning helps

Time-series data with retention: logs, events, metrics, orders older than N months. Dropping a partition is instant, while DELETE of millions of rows is slow and bloats the table.
Queries that filter on the partition key, such as “last 7 days”, which only read the relevant partitions (partition pruning).
Very large tables where maintenance (rebuilds, optimizations) on smaller pieces is easier.

When it doesn’t

Tables of a few million rows with good indexes: an index is usually enough. See database indexing basics.
Queries that don’t filter by the partition key: they must scan all partitions, which can be slower than before.
As a replacement for sharding across servers: partitions live on the same server. See database sharding.

Partition types

Type	Splits by	Typical use
RANGE / RANGE COLUMNS	Value ranges (dates, IDs)	Time-based data
LIST / LIST COLUMNS	Explicit value lists	Regions, categories, tenants
HASH	Hash of an integer expression	Spread rows evenly
KEY	MySQL’s internal hash of columns	Even spread on non-integer keys

RANGE by date: the most common case

CREATE TABLE events (
  id BIGINT NOT NULL AUTO_INCREMENT,
  created_at DATETIME NOT NULL,
  user_id INT NOT NULL,
  type VARCHAR(32) NOT NULL,
  payload JSON,
  PRIMARY KEY (id, created_at),
  KEY idx_user (user_id, created_at)
)
PARTITION BY RANGE COLUMNS (created_at) (
  PARTITION p2026_09 VALUES LESS THAN ('2026-10-01'),
  PARTITION p2026_10 VALUES LESS THAN ('2026-11-01'),
  PARTITION p2026_11 VALUES LESS THAN ('2026-12-01'),
  PARTITION pmax     VALUES LESS THAN (MAXVALUE)
);

The key rule: every unique key, including the primary key, must include all columns used in the partitioning expression. That’s why the primary key here is (id, created_at), not just id.

Partition pruning

Check that a query only reads the partitions it needs:

EXPLAIN SELECT COUNT(*) FROM events
WHERE created_at >= '2026-11-01' AND created_at < '2026-11-08';

The partitions column should list only p2026_11. Wrapping the column in a function (WHERE DATE(created_at) = …) can prevent pruning. Compare ranges directly instead. See MySQL EXPLAIN.

Managing partitions over time

Add next month’s partition by splitting the catch-all pmax:

ALTER TABLE events REORGANIZE PARTITION pmax INTO (
  PARTITION p2026_12 VALUES LESS THAN ('2027-01-01'),
  PARTITION pmax     VALUES LESS THAN (MAXVALUE)
);

Delete old data instantly:

ALTER TABLE events DROP PARTITION p2026_09;      -- removes the data too
ALTER TABLE events TRUNCATE PARTITION p2026_10;  -- empties it, keeps the partition

Automate both with a monthly cron job or a MySQL event, so pmax never fills up with future data. See cron jobs.

You can also archive a partition before dropping it, by exchanging it with an empty table of the same structure: ALTER TABLE events EXCHANGE PARTITION p2026_09 WITH TABLE events_archive;.

LIST example

CREATE TABLE customers (
  id INT NOT NULL,
  region VARCHAR(8) NOT NULL,
  name VARCHAR(100),
  PRIMARY KEY (id, region)
)
PARTITION BY LIST COLUMNS (region) (
  PARTITION p_us   VALUES IN ('US', 'CA'),
  PARTITION p_asia VALUES IN ('HK', 'JP', 'SG'),
  PARTITION p_eu   VALUES IN ('DE', 'FR', 'NL')
);

Inserting a value not in any list fails, so plan for new values.

HASH and KEY

PARTITION BY HASH (user_id) PARTITIONS 8;
PARTITION BY KEY (uuid) PARTITIONS 8;

These spread rows evenly, but don’t help with deleting old data, and only help pruning for equality lookups on the key. They’re rarely the best choice on a single server.

Partitioning an existing table

ALTER TABLE events PARTITION BY RANGE COLUMNS (created_at) ( … );

This rebuilds the whole table, and blocks writes during the copy. For big production tables, use an online schema change tool, or create a new partitioned table and migrate the data in batches. Back up first. See XtraBackup.

Limitations

Every unique key must include the partitioning columns.
Foreign keys aren’t supported on partitioned InnoDB tables.
Maximum 8192 partitions per table. Hundreds of partitions add overhead: monthly is usually better than daily.
Queries without the partition key scan everything.

Quick reference

Task	SQL
Range by date	`PARTITION BY RANGE COLUMNS (created_at)`
Check pruning	`EXPLAIN SELECT …` → `partitions`
Add a partition	`REORGANIZE PARTITION pmax INTO (…)`
Remove old data	`DROP PARTITION p…`
List partitions	`SELECT partition_name, table_rows FROM information_schema.partitions WHERE table_name='events';`

MySQL
Database
Performance