One logical table, many parts
Partitioning splits a single logical table into several physical pieces by a partitioning key, while queries still see one table. InnoDB stores each partition as its own set of files, and the optimizer decides which partitions a query must touch.
Partition types
- RANGE assigns rows to partitions by value ranges, ideal for time series split by month or year.
- LIST assigns rows by an explicit set of key values, such as region codes.
- HASH and KEY spread rows evenly by a hash of the key for balanced distribution.
The big wins
- Partition pruning: when a query filters on the partition key, the optimizer skips partitions that cannot match, scanning far less data.
- Fast bulk delete: dropping an old time range is a single DROP PARTITION instead of a huge DELETE that churns undo and locks.
The catches
- Every unique key, including the primary key, must contain the partition key, which constrains schema design.
- Partitioning on a column that queries do not filter on gives no pruning and only adds overhead.
Key idea
Partitioning divides a table by a key so queries that filter on that key prune irrelevant partitions and old ranges drop instantly, but every unique key must include the partition key.