homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

Mysql - removing expired entries automatically.

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


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)

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)

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)

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)

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.

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved