MySQL table partitioning can dramatically improve query performance and simplify data lifecycle management for large tables — if applied to the right workloads with the right partition key.
When to Partition
- Tables > 50-100 GB where queries consistently filter on one column (date, region, status)
- Time-series data where you need to drop old partitions quickly
- NOT for OLTP tables with random access patterns — partitioning adds overhead for such queries
RANGE Partitioning (Most Common)
sql
CREATE TABLE events (
id BIGINT NOT NULL AUTO_INCREMENT,
event_time DATETIME NOT NULL,
payload JSON,
PRIMARY KEY (id, event_time) -- partition key must be in PK
) PARTITION BY RANGE (YEAR(event_time)) (
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION p2025 VALUES LESS THAN (2026),
PARTITION pmax VALUES LESS THAN MAXVALUE
);Monthly Partitions (Better Granularity)
sql
-- Partition by year*100+month for monthly granularity
CREATE TABLE logs (
id BIGINT NOT NULL,
log_date DATE NOT NULL,
message TEXT,
PRIMARY KEY (id, log_date)
) PARTITION BY RANGE (YEAR(log_date)*100 + MONTH(log_date)) (
PARTITION p202501 VALUES LESS THAN (202502),
PARTITION p202502 VALUES LESS THAN (202503),
PARTITION p202503 VALUES LESS THAN (202504)
);Drop Old Partitions Instantly
sql
-- DROP PARTITION is instant (no DELETE overhead)
ALTER TABLE logs DROP PARTITION p202501;
-- vs DELETE which would be slow and generate binlog events
-- DELETE FROM logs WHERE log_date < '2025-02-01'; -- slow!Verify Partition Pruning
sql
EXPLAIN SELECT * FROM events
WHERE event_time BETWEEN '2025-01-01' AND '2025-01-31';
-- Look for 'partitions' column — should show only p2025, not all partitionsHASH Partitioning for Uniform Distribution
sql
CREATE TABLE user_sessions (
session_id BIGINT NOT NULL,
user_id BIGINT NOT NULL,
data JSON,
PRIMARY KEY (session_id)
) PARTITION BY HASH(user_id) PARTITIONS 8;Key Takeaways
- RANGE partitioning on date columns is the most common and beneficial pattern
ALTER TABLE DROP PARTITIONis instant — ideal for time-series data expiry- Verify partition pruning with EXPLAIN — queries must include the partition key in WHERE
- The partition key must be included in the PRIMARY KEY — a common gotcha
JusDB Can Help
MySQL partitioning has subtle gotchas with indexes, foreign keys, and query patterns. JusDB can design the right partitioning strategy for your tables.