What partitioning is and why it matters

Partitioning splits one logical table into several physical pieces — partitions that live in separate files but look like a single table to queries. The idea resembles separate monthly tables, except the server itself decides which partition to write to and which to read from, with no changes in the application code.

The main payoff is partition pruning: if a query filters on the partitioning column, MySQL scans only the partitions it needs instead of the whole table. For a log table with hundreds of millions of rows, this turns a full-table scan into a scan of a single month.

Partition types: RANGE, LIST, HASH

MySQL and MariaDB offer several types:

Type	Splitting rule	Typical use case
RANGE	By a range of column values	Logs and orders by date
LIST	By a fixed list of values	Data by region or status
HASH	By a hash function of the column	Even distribution without an explicit key
KEY	Like HASH, but using a built-in MySQL function	Quick splitting without a custom formula

RANGE by date is the most common case in practice: old partitions can be dropped entirely with one command instead of a slow DELETE across millions of rows.

Example: partitioning a log table by month

Partitioning is defined at table creation through PARTITION BY:

CREATE TABLE access_log (
  id BIGINT AUTO_INCREMENT,
  created_at DATE NOT NULL,
  ip VARCHAR(45),
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (TO_DAYS(created_at)) (
  PARTITION p2026_01 VALUES LESS THAN (TO_DAYS('2026-02-01')),
  PARTITION p2026_02 VALUES LESS THAN (TO_DAYS('2026-03-01')),
  PARTITION pmax VALUES LESS THAN MAXVALUE
);

Note that the partitioning column must be part of the primary key. This is a MySQL restriction with no workaround — the key has to be designed in advance, before real data shows up.

How to drop old data without DELETE

Instead of a slow DELETE FROM access_log WHERE created_at < '2026-01-01', which writes to the binlog row by row, a whole partition can be dropped at once:

ALTER TABLE access_log DROP PARTITION p2026_01;

The operation is almost instant because it physically removes the partition's file rather than the rows inside it. For log and metrics tables this is the main reason to adopt partitioning. For details on reading a query plan and confirming pruning actually works, see the article on EXPLAIN and reading a plan.

When partitioning does not help

Some scenarios only add complexity without a speed gain:

The table is small — up to a few million rows, where an index is usually enough.
Queries do not filter on the partitioning column, so pruning never kicks in.
Many queries JOIN on a foreign key that does not match the partition column.
Foreign keys pointing at a partitioned table — MySQL does not support them at all.

Maintaining partitions: adding and reorganizing

New partitions for future months should be created ahead of time on a schedule via cron, otherwise inserts into pmax pile up all new rows inside one huge partition:

ALTER TABLE access_log REORGANIZE PARTITION pmax INTO (
  PARTITION p2026_03 VALUES LESS THAN (TO_DAYS('2026-04-01')),
  PARTITION pmax VALUES LESS THAN MAXVALUE
);

After reorganizing, check the row distribution across partitions through information_schema.partitions to make sure data has not piled up in a single partition. If partitions grew larger than the buffer pool was sized for, review its size in the article on my.cnf tuning.

Bottom line: is partitioning worth adopting

Before splitting a table into partitions, answer a few questions:

Is the table growing to the point where old data should be dropped in whole blocks.
Do queries carry a condition on the future partitioning column.
Are you willing to include that column in the primary key.
Does the table need foreign keys — if so, partitioning will not fit.
Is automatic creation of new partitions on a schedule already set up.

MySQL table partitioning: when it actually helps