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

Question

I find myself wanting to delete rows older than (x)-days on a rolling basis in a lot of applications. What is the best way to do this most efficiently on a high-traffic table?

For instance, if I have a table that stores notifications and I only want to keep these for 7 days. Or high scores that I only want to keep for 31 days.

Right now I keep a row storing the epoch time posted and run a cron job that runs once per hour and deletes them in increments like this:

DELETE FROM my_table WHERE time_stored < 1234567890 LIMIT 100 

I do that until mysql_affected_rows returns 0.

I used to do it all at once but that caused everything in the application to hang for 30 seconds or so while INSERTS piled up. Adding the LIMIT worked to alleviate this but I'm wondering if there is a better way to do this.

Answer 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/