1

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?

  • 1
    Bleh! Do you have any kind of job scheduling system? Just run that once nightly or during a down time and delete the records. – Dave Jul 16 '15 at 17:06
0

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

  • 1
    @EdHeal The event is just an example. You could make it EVERY 1 DAY instead of EVERY 1 HOUR or whatever schedule is appropriate. – RolandoMySQLDBA Jul 16 '15 at 17:23
  • I agree - personally I would do it once a week and archive it somewhere – Ed Heal Jul 16 '15 at 17:24
  • Rowland, thanks for the reply. I maybe should have mentioned that I have 1200 of these tables, one for each local version of a website. I'm thinking that I should do as you suggested. To change the script you provided to only run once a day should I set the interval to 24? – Ben Giles Jul 16 '15 at 17:29
  • You could use EVERY 1 DAY, EVERY 24 HOUR, EVERY 1440 MINUTE ,EVERY 86400 SECOND. – RolandoMySQLDBA Jul 16 '15 at 17:36
  • What do you mean by local versions – Ed Heal Jul 16 '15 at 17:41
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.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.