Welcome to WebmasterWorld Guest from 54.147.63.124

Forum Moderators: open

Message Too Old, No Replies

Mysql - removing expired entries automatically.

     
10:35 am on Aug 6, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member sgt_kickaxe is a WebmasterWorld Top Contributor of All Time 5+ Year Member

joined:Apr 14, 2010
posts:3169
votes: 0


Hello,

Scenario - a database contains hundreds of thousands of product sale entries and is continually adding more. Each entry has a field with an expiry date. While it's easy to list the product sales that haven't expired yet the old data remains in the database.

Does mysql provide an easy means of removing the data that reaches its expiry date or is doing it manually (or via cron) the only option?
1:35 pm on Aug 6, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member sgt_kickaxe is a WebmasterWorld Top Contributor of All Time 5+ Year Member

joined:Apr 14, 2010
posts:3169
votes: 0


Just thinking out loud, I know others have done this as a matter of routine but...

Since the table contains a field with an end date the simplest way with my limited skills would be, while requesting the data via php, to use a WHERE command to return only items with an expiry date that is not older than the current date (oi, timezone nightmare but anyway) and if it IS older DELETE the entry.

Assuming there isn't a mysql command to take care of expired data... would requesting data and deleting data from the same page load cause a noticeable performance hit? (480,000 rows to parse)
4:24 pm on Aug 8, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 0


Cron job. :-)

delete from table where expiration <= date_sub(curdate(), interval 2 week)

should clean it up nicely and give you a two week safety zone.
6:06 pm on Aug 8, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member sgt_kickaxe is a WebmasterWorld Top Contributor of All Time 5+ Year Member

joined:Apr 14, 2010
posts:3169
votes: 0


I was looking into setting up a cron job but since there is already an open connection and the table is loaded I added a little function to delete the old stuff before closing the connection.

In php I set today's date at +30 days and delete anything where current date > set date. I can be sure nothing over 30 days old is in the database, most entries expire in 14 so it works.

Performance issue? A whole lot is already going on and asking to DELETE stuff on top of that... asking for trouble?
4:30 pm on Aug 9, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 0


Well, it will definitely slow things a little, but if it's not a problem, it's not a problem. I always pare off non-essential functions for crons so it's one less thing affecting load time.