Data Archiving
Use events to move old data to archive tables, keeping the working set small and queries fast.
MySQL
Intermediate
4 min read
Automated Data Archiving
Example
DELIMITER //
CREATE EVENT archive_old_orders
ON SCHEDULE EVERY 1 DAY STARTS "2024-01-01 02:00:00"
DO
BEGIN
INSERT INTO orders_archive
SELECT * FROM orders WHERE status = "completed" AND completed_at < NOW() - INTERVAL 2 YEAR;
DELETE FROM orders WHERE status = "completed" AND completed_at < NOW() - INTERVAL 2 YEAR;
INSERT INTO event_log VALUES ("archive_old_orders", NOW(), ROW_COUNT());
END//
DELIMITER ; Pro Tip
Archive in small batches (LIMIT 1000) to avoid long transactions and table locks during peak hours.