Table Partitioning in MySQL

Why Partition

Partitioning stores one logical table across several physical partitions. Queries can scan only the partitions that matter instead of the whole table.

Range Partition

CREATE TABLE logs (
  id BIGINT NOT NULL,
  created_at DATETIME NOT NULL,
  message TEXT,
  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)
);

Hash and List

CREATE TABLE audit (id INT, tenant_id INT)
  PARTITION BY HASH (tenant_id) PARTITIONS 8;

LIST partitioning groups rows by explicit value lists, which is ideal for region codes or status columns.

Partition Pruning

The optimizer skips partitions that cannot match the WHERE clause. The partition key must appear in filters to benefit, and every primary key must include it.

Key Points

• RANGE suits time-based data.
• HASH spreads data evenly.
• LIST groups by discrete values.
• Always include the partition key in filters.