MySQL Partitioning Explained: RANGE, LIST, HASH

Partitioning splits one huge table into smaller physical pieces while keeping it logically one table. Queries that target a single partition — like one month of logs — skip the rest entirely.

Key Points

• RANGE: partition by value ranges, typically dates (one partition per month/year)
• LIST: partition by discrete values (region = 'north', 'south')
• HASH/KEY: spread rows evenly when no natural ranges exist
• Partition pruning: WHERE on the partition key scans only relevant partitions
• Drop old data instantly: ALTER TABLE … DROP PARTITION beats DELETE
• The partitioning key must be part of every unique key — plan schema accordingly