Skip to content
Tutorials Center
My learning

Topics

Lesson 50 of 75 ยท Indexes and Performance

Partitioning

Split big MySQL tables with RANGE, LIST, HASH and KEY partitioning, learn partition pruning, the unique key rule, maintenance and when partitioning does not help.

When a table grows to hundreds of millions of rows, two things hurt: queries that touch a lot of data, and housekeeping such as deleting last year's rows. Partitioning splits one logical table into several physical pieces, called partitions, based on the value of a column. Your queries still use the single table name, but MySQL stores each partition separately and can skip the ones it does not need.

Partitioning is often oversold. It rarely makes ordinary lookups faster, and it comes with restrictions. Its real strengths are fast data retention (dropping a whole partition instead of running a huge DELETE) and pruning for queries that filter on the partitioning column. This lesson shows the partition types, the rules, and the cases where you should not use it. The partition syntax needs a real server, so those blocks are read-only. The live examples use the SQLite stand-in to show the idea: mapping rows to partitions, and a table-per-month layout with a view on top.

Four ways to split a table

RANGE Ranges of values, like months or years.
LIST Explicit sets, like region codes.
HASH Even spread by a hash of an integer.
KEY Like HASH, using MySQL's own hash function.
1 / 2

RANGE partitioning

RANGE is the most common type, usually on a date. Each partition holds rows whose value falls below a boundary:

SQL
CREATE TABLE events (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  created_at DATETIME        NOT NULL,
  user_id    INT UNSIGNED    NOT NULL,
  kind       VARCHAR(30)     NOT NULL,
  PRIMARY KEY (id, created_at)
)
PARTITION BY RANGE COLUMNS (created_at) (
  PARTITION p2026_01 VALUES LESS THAN ('2026-02-01'),
  PARTITION p2026_02 VALUES LESS THAN ('2026-03-01'),
  PARTITION p2026_03 VALUES LESS THAN ('2026-04-01'),
  PARTITION pmax     VALUES LESS THAN (MAXVALUE)
);

These statements are read-only here because partitioning is a MySQL server feature. A row goes into the first partition whose boundary is greater than its value, and MAXVALUE is a catch-all so inserts never fail for lack of a partition. RANGE COLUMNS accepts dates and strings directly. Plain RANGE (expr) needs an integer expression such as YEAR(created_at) or TO_DAYS(created_at).

You can see the mapping logic with plain SQL. This example assigns each row to the partition MySQL would choose:

SQL Which partition does each row belong to?

Press Run (or Ctrl + Enter) to see the result here.

Two rows each fall into p2026_01 and p2026_03, one into p2026_02 and one into pmax. On a real server you can read the same information from information_schema.PARTITIONS (TABLE_ROWS per partition).

LIST, HASH and KEY

LIST assigns rows by exact values, which suits categories:

SQL
CREATE TABLE customers (
  id INT NOT NULL, region_code INT NOT NULL, name VARCHAR(80),
  PRIMARY KEY (id, region_code)
)
PARTITION BY LIST (region_code) (
  PARTITION p_europe   VALUES IN (1, 2, 3),
  PARTITION p_americas VALUES IN (10, 11),
  PARTITION p_asia     VALUES IN (20, 21, 22)
);

A row whose region_code is not listed is rejected, so LIST needs care when new values appear.

HASH and KEY spread rows evenly across a fixed number of partitions, which helps when you need to split I/O or table size but have no natural range:

SQL
CREATE TABLE sessions (
  id BIGINT UNSIGNED NOT NULL, user_id INT NOT NULL, data BLOB,
  PRIMARY KEY (id)
)
PARTITION BY HASH (id) PARTITIONS 8;     -- id MOD 8

CREATE TABLE tokens (
  token CHAR(32) NOT NULL, user_id INT NOT NULL,
  PRIMARY KEY (token)
)
PARTITION BY KEY () PARTITIONS 8;        -- hashes the primary key

HASH takes an integer expression and places a row in expression MOD n. KEY uses MySQL's internal hash and accepts any column type (with no column list it uses the primary key). Both make it hard to add partitions later without reshuffling, and neither supports dropping old data by age.

Partition pruning

Pruning means the optimizer excludes partitions that cannot contain matching rows. It only works when the WHERE clause constrains the partitioning column (or expression) in a way the optimizer can use:

SQL
EXPLAIN SELECT COUNT(*) FROM events
WHERE created_at >= '2026-02-01' AND created_at < '2026-03-01';
TEXT
+----+-------------+--------+----------+------+------+
| id | select_type | table  | partitions | type | rows |
+----+-------------+--------+------------+------+------+
|  1 | SIMPLE      | events | p2026_02   | ALL  | 41000 |
+----+-------------+--------+------------+------+------+

The partitions column of EXPLAIN lists the partitions that will be read, here just p2026_02. The output is abbreviated for width. Without a condition on created_at, it would list all of them, and MySQL would probe every partition. Pruning also fails when you wrap the column in a function the partitioning expression does not match, so write plain range conditions as above. The EXPLAIN lesson explains the other columns.

The unique key rule

This is the restriction that catches most people: every unique key on a partitioned table, including the primary key, must include all the columns used in the partitioning expression. MySQL cannot check uniqueness across partitions without reading them all, so it demands that the partition column be part of the key.

SQL
CREATE TABLE events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id)
)
PARTITION BY RANGE COLUMNS (created_at) (
  PARTITION p0 VALUES LESS THAN ('2026-02-01'),
  PARTITION pmax VALUES LESS THAN (MAXVALUE)
);
-- ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

The fix is a composite primary key such as PRIMARY KEY (id, created_at), as in the first example. The consequence is that id alone is no longer unique-enforced by the database: two rows with the same id but different dates are both allowed. If you need a globally unique ID, generate it in the application (or with an auto-increment that you trust) and accept that the schema does not enforce it.

Other limits to know:

  • No foreign keys. An InnoDB partitioned table cannot have foreign keys, and cannot be referenced by one.
  • No full-text indexes on partitioned InnoDB tables, and no spatial indexes.
  • At most 8,192 partitions per table, and thousands of partitions slow down opening the table and some statements.
  • Every partition needs an index probe, so a lookup on a non-partition column checks every partition.

Maintenance: the main benefit

Dropping old data from a huge table with DELETE ... WHERE created_at < ... is slow, creates enormous undo and binary log volume and leaves the table fragmented. Dropping a partition is a quick metadata and file operation:

SQL
-- retire the oldest month
ALTER TABLE events DROP PARTITION p2026_01;

-- make room for the next month: split the catch-all partition
ALTER TABLE events REORGANIZE PARTITION pmax INTO (
  PARTITION p2026_04 VALUES LESS THAN ('2026-05-01'),
  PARTITION pmax     VALUES LESS THAN (MAXVALUE)
);

ALTER TABLE events TRUNCATE PARTITION p2026_02;                 -- empty it, keep it
ALTER TABLE events EXCHANGE PARTITION p2026_03 WITH TABLE events_archive;  -- swap data

A scheduled job (or the event scheduler) usually adds next month's partition ahead of time and drops the oldest one. Forgetting to add partitions is a classic outage: if you have no MAXVALUE partition, an insert beyond the last boundary fails with error 1526 (no partition for the value).

The same idea can be practiced live with one table per month and a view that unions them. This mimics what a partitioned table does, and dropping the oldest "partition" is a quick DROP TABLE:

SQL A table per month, a view on top, and a fast purge

Press Run (or Ctrl + Enter) to see the result here.

The count goes from 5 to 3. The difference from real partitioning: here the application must know the table names and rebuild the view, whereas MySQL does all of that for you inside one table.

When partitioning helps, and when it does not

Use it when the table is very large, you mostly query recent or specific ranges of the partition column, and you want cheap retention (logs, events, time series, audit tables).

Skip it when the table fits comfortably in memory, queries filter on other columns, you need foreign keys, or you hoped to "speed up the database". A well-chosen index nearly always beats partitioning for point lookups. If your main need is scale beyond one server, partitioning is not the tool; look at sharding in the high availability lesson.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the strongest everyday benefit of RANGE partitioning by date?

    Partition maintenance such as DROP PARTITION is fast. Partitioning does not speed up arbitrary lookups and it restricts foreign keys.
  2. 2. What must be true of a primary key on a partitioned table?

    Every unique key, including the primary key, must contain all partitioning columns, otherwise MySQL cannot enforce uniqueness without reading all partitions.
  3. 3. How can you see which partitions a query will read?

    EXPLAIN shows a partitions column. A pruned query lists only the partitions that can hold matching rows.
  4. 4. Which is NOT supported on an InnoDB partitioned table?

    InnoDB partitioned tables cannot have foreign keys and cannot be referenced by them.

Summary

  • Partitioning splits one table into physical pieces by RANGE, LIST, HASH or KEY. Use RANGE COLUMNS for dates.
  • Pruning skips partitions when the WHERE clause constrains the partitioning column. EXPLAIN shows which partitions are read.
  • Every unique key, including the primary key, must include the partitioning columns, and foreign keys are not allowed.
  • The big win is maintenance: DROP PARTITION, REORGANIZE PARTITION, TRUNCATE PARTITION and EXCHANGE PARTITION instead of giant deletes.
  • Add future partitions on a schedule, and keep a MAXVALUE partition as a safety net.
  • Do not expect partitioning to speed up lookups on other columns. Indexes do that.

Next: Stored Procedures and Functions, where we put logic inside the database.