Welcome to WebmasterWorld Guest from 54.163.35.238

Forum Moderators: open

Message Too Old, No Replies

Mysql - removing expired entries automatically.

     

Sgt_Kickaxe

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

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



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

1:35 pm on Aug 6, 2011 (gmt 0)

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



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

4:24 pm on Aug 8, 2011 (gmt 0)

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



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

6:06 pm on Aug 8, 2011 (gmt 0)

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



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

4:30 pm on Aug 9, 2011 (gmt 0)

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



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.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month