Table partitioning: splitting large tables by range or list

What Is Partitioning?

Partitioning splits a table's data into separate physical segments (partitions) while keeping them logically one table. Queries on a partitioned table skip irrelevant partitions — this is called partition pruning and can cut query time dramatically on large tables.

Range Partitioning by Date

-- MySQL range partitioning on created_at year
CREATE TABLE orders (
  order_id   INT NOT NULL AUTO_INCREMENT,
  customer_id INT NOT NULL,
  total      DECIMAL(10,2),
  created_at DATE NOT NULL,
  PRIMARY KEY (order_id, created_at)
)
PARTITION BY RANGE (YEAR(created_at)) (
  PARTITION p2022 VALUES LESS THAN (2023),
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION pmax  VALUES LESS THAN MAXVALUE
);

List and Hash Partitioning

• LIST partitions by discrete values: partition orders by country code or region.
• HASH distributes rows evenly across N partitions using a hash of a column — good for even load without a natural range key.

Limitations

• Foreign keys cannot span partitions (MySQL limitation).
• The partition key must be part of every unique and primary key.
• Add a MAXVALUE (catch-all) partition and rotate partitions on a schedule.