MySQL 8 Cookbook

Archiving tables

Sometimes, you do not want to keep older data and wish to delete it. If you want to delete all the rows which were last accessed over a month ago, if the table is small (<10k rows), you can straight away use the following:

DELETE FROM <TABLE> WHERE last_accessed<DATE_ADD(NOW(), INTERVAL -1 MONTH)

What happens if the table is big? You know InnoDB creates an UNDO log to restore failed transactions. So all the deleted rows are saved in the UNDO log space to be used to restore in case the DELETE statement aborts in between. Unfortunately, if the DELETE statement is aborted in between, InnoDB copies the rows from the UNDO log space to table, which can make the table inaccessible.

To overcome this behavior, you can LIMIT the number ...