4

I want to make a table where the entries expire 24 hours after they have been inserted in PHP and MySQL.

Ideally I want to run a "deleting process" every time a user interacts with my server, that deletes old entries. Since this is more frequent you should it will not have large amounts of data to delete so it should only take a few milliseconds.

I have given each entry a date/time added value.

How would I do this?

  • 1
    What do you mean by *every time a user interacts with my server*? – Mike Christensen Sep 13 '12 at 16:30
  • Look at the answers of this (duplicate) question: http://stackoverflow.com/questions/9472167/what-is-the-best-way-to-delete-old-rows-from-mysql-on-a-rolling-basis – Rex Sep 13 '12 at 16:30
  • Which part is unclear to you? PHP? SQL? – D'Arcy Rittich Sep 13 '12 at 16:30
  • With a DELETE statement... hem – Bgi Sep 13 '12 at 16:30
  • i mean that every time a user runs a specific query, it will remove all entries older than 24 hours and then run the specific query. I would rather it be done this way than with an event scheduler. I would like it to be done in php. –  Sep 13 '12 at 18:30

3 Answers3

12

You could use MySQL's event scheduler either:

  • to automatically delete such records when they expire:

    CREATE EVENT delete_expired_101
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 24 HOUR DO
DELETE FROM my_table WHERE id = 101;

  • to run an automatic purge of all expired records on a regular basis:

    CREATE EVENT delete_all_expired
ON SCHEDULE EVERY HOUR DO
DELETE FROM my_table WHERE expiry < NOW();
eggyal
  • 122,705
  • 18
  • 212
  • 237
  • WOW! I didn't even ask the question and you came up with something completely new to me! Thanks so much! – Stefan Sep 13 '12 at 16:41
  • I had to tell MySQL a specific number of 'every X hours' even if I just wanted every hour. eg `ON SCHEDULE EVERY 1 HOUR DO` – Nick Nov 11 '15 at 23:48
2
  1. you shouldn't do a delete process when a user interacts. it slows down things, you should use a cronjob (every minute / hour)
  2. you'll want to index the added timestamp value and then run DELETE FROM table WHERE added < FROM_UNIXTIME(UNIX_TIMESTAMP()-24*60*60)
  3. maybe you'll want to checkout Partitions, which divide the table into different tables, but it behaves as one table. The advantage is that you don't need to delete the entries and you'll have seperate tables for each day.
  4. i think that YOU think that much data slows down tables. Maybe you should use EXPLAIN (MySQL Manual) and optimize your SELECT queries using indexes (MySQL Manual)
  5. UPDATE Check out eggyal's answer - This is another approach worth taking a look.
Community
  • 1
  • 1
Stefan
  • 2,028
  • 2
  • 36
  • 53
  • 1
    You would also need to restrict your queries on the table if you need EXACT 24 hour precision. But IMO this is the best approach. This table would also probably best suited for InnoDB engine if it is not already. – Mike Brant Sep 13 '12 at 16:41
0

You can look into using Cron Job, http://en.wikipedia.org/wiki/Cron Make it run once every 24 hours when it matches your requirement.

This will help

Delete MySQL row after time passes

Community
  • 1
  • 1
Guo Hong Lim
  • 1,690
  • 3
  • 13
  • 20