Partitioning divides one table into several physical pieces by a rule on a column, while it continues to look like one table. RANGE by date is the common form: a partition per month or per year.
Its real benefit is partition pruning - a query filtered on the partitioning column reads only the relevant partitions, so a query for last month ignores five years of data. The second, and often larger, benefit is maintenance: dropping a whole partition is near-instant and reclaims the space immediately, which makes "delete everything older than two years" a DROP PARTITION instead of a DELETE of fifty million rows.
The limits are strict and worth knowing before you plan around it. Every unique key, including the primary key, must contain the partitioning column - which frequently forces a composite primary key. Foreign keys are not supported on partitioned tables at all. And a query that does not filter on the partitioning column touches every partition, which can be slower than one unpartitioned table.
Before partitioning, try the ordinary things, because they fix far more cases: the right indexes, archiving old rows to a separate table, a summary table for reports, and a read replica. Partitioning is for tables that are genuinely enormous and queried along one obvious axis - which is almost always time.
-- RANGE by date. Note the primary key: every unique key MUST contain
-- the partitioning column, which usually forces a composite key.
CREATE TABLE events (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
occurred_on DATE NOT NULL,
kind VARCHAR(40) NOT NULL,
payload JSON NULL,
PRIMARY KEY (id, occurred_on),
KEY idx_events_kind (kind, occurred_on)
) ENGINE = InnoDB
PARTITION BY RANGE (YEAR(occurred_on)) (
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION p2025 VALUES LESS THAN (2026),
PARTITION p2026 VALUES LESS THAN (2027),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
-- Pruning: this reads one partition. "partitions: p2026" in the plan.
EXPLAIN SELECT COUNT(*) FROM events
WHERE occurred_on BETWEEN '2026-01-01' AND '2026-12-31';
-- No filter on the partitioning column: every partition is read, which
-- can be slower than not partitioning at all.
EXPLAIN SELECT COUNT(*) FROM events WHERE kind = 'login';
-- The maintenance win: near-instant, and the space comes back at once.
ALTER TABLE events DROP PARTITION p2024;
ALTER TABLE events ADD PARTITION (PARTITION p2027 VALUES LESS THAN (2028));
SELECT partition_name, table_rows,
ROUND(data_length / 1024 / 1024) AS mb
FROM information_schema.partitions
WHERE table_schema = DATABASE() AND table_name = 'events';
-- Try these first. They fix far more cases than partitioning does:
-- the right index, an archive table, a summary table, a read replica.