I have a mysql table that tracks page views. Each row includes a date stamp. I want to remove all rows that are over 30 days old on a regular basis. Could, or should, I trigger an action on each insertion of a new row that would delete the rows that are over 30 days old? Or would I be better off adding a separate delete action along with each new insertion?
You do not want a trigger to micromanage such deletions. I have an old post where I created a MySQL Event to delete rows for you (MySQL Event does not run)
Here is an event to delete data older that 30 days every hour starting from midnight tonight
USE mydata DELIMITER $$ CREATE EVENT delete_30_day_old_data ON SCHEDULE EVERY 1 HOUR STARTS CURDATE() + INTERVAL 1 DAY + INTERVAL 0 SECOND ON COMPLETION PRESERVE DO BEGIN DELETE FROM mytable WHERE datetime_column < NOW() - INTERVAL 30 DAY; END $$ DELIMITER ;
I will be totally independent of any triggers
Of course, you must enable the event_scheduler
First, add this to
Then, login to mysql as root and run
SET GLOBAL event_scheduler = 'ON';
GIVE IT A TRY !!!
I don't think it's critical to have the old rows at the very moment they turn 30 days old - a few seconds/minutes/hours delay will probably not hurt you at all. Therefore I'd remove old rows with some scheduled task/query run at some fixed interval.
Otherwise you'll end up running additional query for every page view that you get.
Just get the front end to display the data from the last 30 days. The delete at lesuire perhaps once a week depending on resources