What is the best way to delete old rows from MySQL on a rolling basis?

Solution 1:

Try creating Event that will run on database automatically after the time interval you want.

Here is an Example: If you want to delete entries that are more than 30 days old from some table 'tableName', having column entry 'datetime'. Then following query runs every day which will do required clean-up action.

CREATE EVENT AutoDeleteOldNotifications
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY 
ON COMPLETION PRESERVE
DO 
DELETE LOW_PRIORITY FROM databaseName.tableName WHERE datetime < DATE_SUB(NOW(), INTERVAL 30 DAY)

We need to add ON COMPLETION PRESERVE to keep the event after each run. You can find more info here: http://www.mysqltutorial.org/mysql-triggers/working-mysql-scheduled-event/

Solution 2:

Check out MySQL Partitioning:

Data that loses its usefulness can often be easily removed from a partitioned table by dropping the partition (or partitions) containing only that data. Conversely, the process of adding new data can in some cases be greatly facilitated by adding one or more new partitions for storing specifically that data.

See e.g. this section to get some ideas on how to apply it:

MySQL Partition Pruning

And this one:

Partitioning by dates: the quick how-to

What is the best way to delete old rows from MySQL on a rolling basis?

Solution 1:

Solution 2:

Related

Recent Posts