O'Reilly logo

MySQL 8 Cookbook by Karthik Appigatla

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required