Partitioning and large-table strategies

Partitioning and large-table strategies. When a table holds hundreds of millions of rows, even good indexes struggle. Partitioning splits one logical table into smaller physical chunks so MySQL can prune partitions at query time and manage data lifecycle more cleanly.

What partitioning does

Partitioning is not sharding across servers – it stays on one MySQL instance. Rows are routed into partitions by a rule: range on a date column, list on a region code, hash on an ID, and so on. The optimiser can skip whole partitions when your WHERE clause matches the partition key.

CREATE TABLE events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id INT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL,
  payload JSON,
  PRIMARY KEY (id, created_at)
)
PARTITION BY RANGE (TO_DAYS(created_at)) (
  PARTITION p202601 VALUES LESS THAN (TO_DAYS('2026-02-01')),
  PARTITION p202602 VALUES LESS THAN (TO_DAYS('2026-03-01')),
  PARTITION p202603 VALUES LESS THAN (TO_DAYS('2026-04-01')),
  PARTITION pmax VALUES LESS THAN MAXVALUE
);

Notice the primary key includes created_at. Every unique key on a partitioned table must include all columns in the partition expression. That constraint catches people out.

When partitioning helps

• Time-series data – events, logs, metrics – where queries filter by date range
• Archival and retention – drop old partitions instead of DELETE on billions of rows
• Maintenance windows – optimise or rebuild one partition at a time

When it does not help

Partitioning is not a substitute for indexes. If your queries do not filter on the partition key, MySQL scans every partition – often worse than a single table. Small tables gain nothing. Cross-partition JOINs and global uniqueness outside the partition key get awkward fast.

-- Good: partition pruning
SELECT COUNT(*) FROM events
WHERE created_at >= '2026-03-01' AND created_at < '2026-04-01';

-- Bad: no pruning - scans all partitions
SELECT * FROM events WHERE tenant_id = 42;

Managing partitions

-- Add next month
ALTER TABLE events REORGANIZE PARTITION pmax INTO (
  PARTITION p202604 VALUES LESS THAN (TO_DAYS('2026-05-01')),
  PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- Drop data older than retention policy
ALTER TABLE events DROP PARTITION p202601;

-- Inspect layout
SELECT PARTITION_NAME, TABLE_ROWS
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'events';

Large-table strategies without partitioning

Try these before reaching for partitions:

• Right-size indexes – composite keys aligned to query patterns (see the intermediate index strategy tutorial)
• Archive cold rows to a history table – simpler than partition gymnastics if access patterns split cleanly
• Summary tables for reporting – aggregate nightly instead of scanning raw events every dashboard load
• Covering indexes and selective columns – wide rows hurt cache efficiency
• Batch deletes with LIMIT in a loop instead of one massive DELETE that locks the table

-- Safer large delete pattern (run until 0 rows affected)
DELETE FROM events
WHERE created_at < '2025-01-01'
LIMIT 5000;

Practical checklist

• Measure with EXPLAIN – confirm partition pruning on real queries
• Automate partition creation and drops – manual monthly ALTER is how incidents happen
• Document retention policy alongside schema – ops needs to know what can be dropped
• Test ALTER on staging – reorganising partitions on huge tables still takes time and I/O

The next tutorial covers replication and read replicas – scaling reads and planning for failure.