CODEWITHSUNDEEP
phpmysqlhtml
user1669109
user1669109

Reputation:

SQL entries that expire after 24 hours

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?

Upvotes: 4

Views: 8041

Answers (3)

Stefan
Stefan

Reputation: 2068

  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.

Upvotes: 2

eggyal
eggyal

Reputation: 125855

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();

Upvotes: 12

Guo Hong Lim
Guo Hong Lim

Reputation: 1710

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

Upvotes: 0

Related Questions