homepage Welcome to WebmasterWorld Guest from 54.163.72.86
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.
Sgt_Kickaxe




msg:4348554
 10:35 am on Aug 6, 2011 (gmt 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?

 

Sgt_Kickaxe




msg:4348590
 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)

rocknbil




msg:4349058
 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.

Sgt_Kickaxe




msg:4349129
 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?

rocknbil




msg:4349557
 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