Partitioning Large MySQL Tables for Performance

Partitioning splits a huge table into smaller physical pieces while keeping it queryable as one logical table — useful once a table grows into the hundreds of millions of rows and simple indexing isn’t enough anymore.

CREATE TABLE events (
    id BIGINT AUTO_INCREMENT,
    event_type VARCHAR(50),
    created_at DATE,
    PRIMARY KEY (id, created_at)
)
PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p2026 VALUES LESS THAN (2027),
    PARTITION pfuture VALUES LESS THAN MAXVALUE
);

-- Queries filtering on the partition key can skip entire partitions
SELECT * FROM events WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01';

-- Dropping an old partition is near-instant vs a slow DELETE
ALTER TABLE events DROP PARTITION p2024;

Partitioning shines specifically for time-series-style data where you regularly need to purge old data — dropping a partition is essentially instant, versus a DELETE that would otherwise take hours and generate a massive amount of undo log.

Partitioning Large MySQL Tables for Performance

Recommended

Categories