Creating a partitioned table is only the beginning. As time progresses, old partitions must be retired, new date brackets must be provisioned, and data must be archived.
MySQL provides specialized ALTER TABLE commands for partition maintenance that run in seconds rather than hours.
1. Instant Data Purging with DROP PARTITION
In standard unpartitioned tables, deleting 50 million old rows via DELETE FROM logs WHERE log_date < '2023-01-01'; is disastrous:
Generates 50 million undo log entries
Locks table rows for hours
Squeezes disk I/O and leaves fragmented table space
With Range Partitioning, purging an entire year's data is an instant DDL metadata drop:
1-- Instantly drops 50 million rows and frees disk space in 0.05 seconds!
2ALTERTABLE orders_range DROPPARTITION p2023;
2. Adding New Partitions (ADD PARTITION)
1-- Adding a partition to a table without MAXVALUE
2ALTERTABLE orders_range
3ADDPARTITION(PARTITION p2026 VALUES LESS THAN (2027));
If your table has a MAXVALUE partition, ADD PARTITION fails. You must use REORGANIZE PARTITION to split the catch-all: