TopolearnPartitioning makes dropping old data instant on huge tables, but its restrictions shape the whole schema.
MySQL partitions a table into separately stored pieces declared in CREATE TABLE ... PARTITION BY:
RANGE/RANGE COLUMNS: by ranges, typically dates.RANGE COLUMNS(created_at)works directly on date columns; plainRANGEneeds an integer expression such asTO_DAYS(created_at).LIST/LIST COLUMNS: by discrete values.HASH/KEY: spread rows evenly by a hash.
The optimizer prunes partitions that can't match the WHERE clause; EXPLAIN shows which ones are read in the partitions column.
Restrictions to design around:
- Every unique key, including the primary key, must include all columns of the partitioning expression. A table partitioned by
created_atneeds a primary key like(id, created_at). - Partitioned InnoDB tables can't have foreign keys and can't be referenced by them.
- Each partition is a separate tablespace file; thousands of partitions cost memory and open files.
Maintenance operations are the main benefit: ALTER TABLE t DROP PARTITION p2025_01 deletes a month instantly; REORGANIZE PARTITION splits a catch-all MAXVALUE partition to add new ranges; EXCHANGE PARTITION swaps a partition with a standalone table for fast bulk loads or archiving.
Partitioning doesn't speed up primary-key lookups; use it for data lifecycle and pruning on time-based queries.
Example
CREATE TABLE event (
id BIGINT NOT NULL AUTO_INCREMENT,
created_at DATETIME NOT NULL,
kind VARCHAR(20) NOT NULL,
PRIMARY KEY (id, created_at) -- must include the partitioning column
)
PARTITION BY RANGE COLUMNS (created_at) (
PARTITION p2026_04 VALUES LESS THAN ('2026-05-01'),
PARTITION p2026_05 VALUES LESS THAN ('2026-06-01'),
PARTITION pmax VALUES LESS THAN (MAXVALUE)
);
INSERT INTO event (created_at, kind) VALUES ('2026-04-20', 'login'), ('2026-05-02', 'click');
EXPLAIN SELECT COUNT(*) FROM event WHERE created_at >= '2026-05-01' AND created_at < '2026-06-01';
-- partitions: p2026_05
ALTER TABLE event REORGANIZE PARTITION pmax INTO (
PARTITION p2026_06 VALUES LESS THAN ('2026-07-01'),
PARTITION pmax VALUES LESS THAN (MAXVALUE));
ALTER TABLE event DROP PARTITION p2026_04; -- instant retentionRemember
- RANGE COLUMNS on a date is the usual time-series layout
- Unique keys must include the partitioning columns; no foreign keys
- DROP and REORGANIZE PARTITION make data lifecycle cheap
Watch out for
- Partitioning a table that relies on foreign keys, which MySQL doesn't allow.