To trigger or not to trigger- That is the question

Question

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?

Answer 1

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.

Answer 2

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

[mysqld]
event_scheduler=ON

Then, login to mysql as root and run

SET GLOBAL event_scheduler = 'ON';

GIVE IT A TRY !!!

Answer 3

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