Partitioning

Partitioning

Partitioning by RANGE, LIST, HASH, and KEY

A partitioned table is one logical table stored as several InnoDB tablespaces; a function of each row picks its partition. RANGE (and RANGE COLUMNS) assigns value bands with VALUES LESS THAN, the usual choice for dates; LIST assigns explicit sets with VALUES IN, such as regions; HASH spreads rows by an integer expression modulo N; KEY does the same with MySQL 524 's own hash of any columns. Every unique key must contain every column the function uses (otherwise ERROR 1503), and InnoDB refuses foreign keys on partitioned tables (ERROR 1506):

Order history partitioned by yearSQL

CREATE TABLE order_history (
  id          INT UNSIGNED NOT NULL,
  customer_id INT UNSIGNED NOT NULL,
  total       DECIMAL(10,2) NOT NULL,
  ordered_at  DATETIME NOT NULL,
  PRIMARY KEY (id, ordered_at),
  KEY (customer_id)
)
PARTITION BY RANGE (YEAR(ordered_at)) (
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION p2025 VALUES LESS THAN (2026),
  PARTITION p2026 VALUES LESS THAN (2027),
  PARTITION pmax  VALUES LESS THAN MAXVALUE
);

A recursive CTE then inserted 200,000 rows, one every 9 minutes from 2023. Partition pruning is the payoff: when WHERE bounds the partitioning column, only matching partitions are opened. 9.7's default TREE format of EXPLAIN hides partitions, so ask for the traditional one:

Which partitions two equivalent queries readSQL

Q="SELECT COUNT(*), SUM(total) FROM order_history WHERE"
for W in "ordered_at >= '2025-01-01' AND ordered_at < '2025-07-01'" "YEAR(ordered_at) = 2025"
do mysql -P 3317 -E shop -e "EXPLAIN FORMAT=TRADITIONAL $Q $W" | grep -E "partitions|rows"
done

Output

   partitions: p2025
         rows: 58656
   partitions: p2024,p2025,p2026,p2027,pmax
         rows: 83106

The bare-column range reads only p2025; YEAR(ordered_at) = 2025 asks for the same rows but cannot be pruned, so it reads every partition (the sargable rule of Sargable Predicates). The partition list reflects the maintenance, run just before:

Adding next year, dropping the oldest, emptying oneSQL

ALTER TABLE order_history REORGANIZE PARTITION pmax INTO (
  PARTITION p2027 VALUES LESS THAN (2028),
  PARTITION pmax  VALUES LESS THAN MAXVALUE);
ALTER TABLE order_history DROP PARTITION p2023;
ALTER TABLE order_history TRUNCATE PARTITION p2024;

COUNT(*) fell from 200,000 to 83,041, on the replica too. DROP PARTITION removes a year as one file, where DELETE would log 58,000 row changes: retention is the best reason to partition. Add p2028 before New Year, or its rows land in pmax.

MENU