Why should you care?

orders table 500M rows — monthly RANGE partitions drop old month instantly vs DELETE marathon.

See it live — copy this example

Run in MySQL Workbench or the mysql CLI.

CREATE TABLE orders_part (
  order_id INT UNSIGNED NOT NULL,
  placed_at DATE NOT NULL,
  total_inr DECIMAL(12,2) NOT NULL,
  PRIMARY KEY (order_id, placed_at)
) PARTITION BY RANGE (TO_DAYS(placed_at)) (
  PARTITION p2024 VALUES LESS THAN (TO_DAYS('2025-01-01')),
  PARTITION p2025 VALUES LESS THAN (TO_DAYS('2026-01-01')),
  PARTITION pmax VALUES LESS THAN MAXVALUE
);

What happened?

• Rows route to partition by placed_at.
• Query with placed_at range touches only matching partitions — partition pruning in EXPLAIN partitions column.

Practice next

1. CREATE partitioned table in dev DataFlow.
2. INSERT rows in different years.
3. EXPLAIN SELECT with date filter — partitions pruned count.
4. LIST partition by region code column.
5. Compare query time with vs without pruning EXPLAIN.

Remember

Partitions prune scans on big tables. PK must include partition key. DROP PARTITION for fast archival.

DataFlow order archive

Legal retention 7 years — drop monthly partitions older than policy.

Outcome: Disk freed in seconds not days of DELETE.