Partitioning Tables in MySQL: When and How

Developers ask me to "partition the big table" as if it were a speed switch, and then they're disappointed when queries don't get faster. It pays off in two situations: you regularly delete or archive by range (logs, events, measurements) and want DROP PARTITION instead of a slow DELETE, or nearly every query filters on the partitioning column so the optimizer can skip whole partitions. Otherwise a good index usually wins.

This covers MySQL 5.7 and 8.0 with InnoDB. 8.0 removed the generic partitioning engine, so only InnoDB (and NDB) tables can be partitioned; convert any MyISAM partitions before upgrading. If you can't name the one column your queries filter on, stop here.

Choosing the column and type

The rule that bites hardest: every unique key of a partitioned table, including the primary key, must include all columns of the partitioning expression. For an event table keyed by id alone, that means a primary key of (id, created_at) if you partition by created_at.

The common types:

• RANGE / RANGE COLUMNS – by date or increasing numeric ranges; the right choice for time series.
• LIST / LIST COLUMNS – by explicit discrete values such as region codes.
• HASH / KEY – spread rows evenly by a hash; helps only with insert contention on very hot tables and offers no pruning for range queries.

A table partitioned by month

CREATE TABLE events (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  created_at DATETIME NOT NULL,
  user_id    INT UNSIGNED NOT NULL,
  type       VARCHAR(32) NOT NULL,
  payload    JSON,
  PRIMARY KEY (id, created_at),
  KEY idx_user (user_id, created_at)
) ENGINE=InnoDB
PARTITION BY RANGE COLUMNS (created_at) (
  PARTITION p201909 VALUES LESS THAN ('2019-10-01'),
  PARTITION p201910 VALUES LESS THAN ('2019-11-01'),
  PARTITION p201911 VALUES LESS THAN ('2019-12-01'),
  PARTITION p201912 VALUES LESS THAN ('2020-01-01'),
  PARTITION pmax    VALUES LESS THAN (MAXVALUE)
);

RANGE COLUMNS lets you compare a DATETIME directly. Plain RANGE needs an integer expression such as TO_DAYS(created_at), which works but makes the definition harder to read. The pmax catch-all prevents inserts from failing when you forget to add next month's partition; the trade-off is that rows landing in it have to be moved with REORGANIZE PARTITION later.

Adding and dropping partitions

Adding the next month, when a MAXVALUE partition exists, means splitting it:

ALTER TABLE events REORGANIZE PARTITION pmax INTO (
  PARTITION p202001 VALUES LESS THAN ('2020-02-01'),
  PARTITION pmax    VALUES LESS THAN (MAXVALUE)
);

If pmax is empty this is instant. Dropping old data is the main payoff:

ALTER TABLE events DROP PARTITION p201909;

This removes millions of rows in well under a second, without undo logging, without replication lag, and it frees the space immediately because each partition is its own .ibd file. Compare that to DELETE FROM events WHERE created_at < '2019-10-01', which on a large table runs for many minutes and bloats the undo log.

To archive rather than delete, on 5.7 and later you can swap a partition with a table of identical structure:

CREATE TABLE events_archive_201909 LIKE events;
ALTER TABLE events_archive_201909 REMOVE PARTITIONING;
ALTER TABLE events EXCHANGE PARTITION p201909 WITH TABLE events_archive_201909;

Does pruning happen?

Pruning is what makes queries faster, and it only happens when the WHERE clause lets the optimizer decide which partitions can hold matching rows:

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

The partitions column of the output must list only p201911. Compare:

EXPLAIN SELECT COUNT(*) FROM events WHERE user_id = 4411;

Here partitions lists every partition. Each partition's idx_user index is searched separately – five index lookups instead of one. Queries that do not filter on the partition column get slightly slower, not faster, which is why I say to check before committing.

Converting an existing table

Partitioning an existing table rebuilds all of it:

ALTER TABLE events DROP PRIMARY KEY, ADD PRIMARY KEY (id, created_at);
ALTER TABLE events PARTITION BY RANGE COLUMNS (created_at) ( ... );

On a large production table use pt-online-schema-change from Percona Toolkit for both statements, and make sure there is free disk space for a second copy of the table.

Sanity check

SELECT partition_name, table_rows, ROUND(data_length/1024/1024) AS data_mb
FROM information_schema.partitions
WHERE table_schema = 'shop' AND table_name = 'events';

Each partition should hold roughly the rows you expect for its month, and pmax should be empty or nearly so. Run your top three queries with EXPLAIN and check the partitions column for each.

Errors

• ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function – See the rule on unique keys above; widen the primary key or choose a different column.
• ERROR 1526 (HY000): Table has no partition for value from column_list – A row falls outside every range and there is no MAXVALUE partition. Add the partition or a catch-all.
• ERROR 1506 (HY000): Foreign keys are not yet supported in conjunction with partitioning – Partitioned InnoDB tables cannot have or be referenced by foreign keys. Either drop the constraint and enforce it in the application, or do not partition that table.
• ERROR 1737 (HY000): Found a row that does not match the partition during EXCHANGE PARTITION – The archive table contains rows outside the partition's range; it must be empty or exactly matching. Add WITHOUT VALIDATION on 5.7.5+ only if you are certain.
• Query is slower after partitioning – No pruning. Either rewrite the query to include the partition column or accept that this table was not a good candidate.

I partition by time only when I need to age out data. As a generic speed-up it mostly adds complexity.