Archiving old records out of MySQL database

I'm writing a PHP / MySQL application.

There's a table in the MySQL database which gets a lot of data stored in it - mostly logs and status reports. The table has a number of INTEGER and VARCHAR(200), fields, and then a big TEXT field. Every record that gets inserted usually has 30-50kb of text in that TEXT field. This table is getting thousands of records written to it every day. And it's growing... by roughly 100-200 Mb every day. It's grown to over 8GB in size.

So... what we'd like to do is to archive and/or delete the old records after a certain period of time. So say after 30 days, the records get archived to another table or database or server... and then after 365 days, they get deleted entirely.

What's the right way to go about doing that in PHP/MySQL?

Obviously I know how to write a SQL query that does a "DELETE * FROM mytable WHERE DATEDIFF(....)", and I can run that on a Cron Job. But.... is that all there is to it?

Are there other considerations or performance issues I need to be aware of when using MySQL to pass this kind of data into, and then out, of the database?

Is my table size going to grow wildly out of control, despite me deleting the rows? Or is there some kind of "dump" or "clean" or "shrink" operations I need to perform on the database as general maintenance? Or does MySQL handle all of that all on it's own?
LVL 31
Who is Participating?
Julian HansenConnect With a Mentor Commented:
Obviously I know how to write a SQL query that does a "DELETE * FROM mytable WHERE DATEDIFF(....)", and I can run that on a Cron Job. But.... is that all there is to it?
Pretty much. MySQL will manage the storing of the live data - and will reorganise itself after a delete.
What you would probably do is write a cron script (assuming you are on Linux - or a Windows Scheduler task if on Windows) that would run on a regular basis that would do the following

1. Remove all records from the archive that are older than 365 days
2. Move to the archive from live all records that are older than 30 days.

You could choose to run this cron job however often as necessary (daily, weekly, monthly).

Simply write your script in PHP and then open crontab -e and fill in your scheduling and path to your script.

In Windows either look at the command line AT help or use the GUI under Administration Tools (Pre Windows 8 - have no idea where MS moved the furniture to in W8).

Other than that you are good to go - MySQL should do the rest for you.
Ray PaseurCommented:
You might add a column of type TIMESTAMP to the table.  The timestamp will be updated whenever an INSERT or UPDATE query changes the values in a row of the table.  Then you can prune the table with a DELETE FROM query with a WHERE clause that shows a value in the TIMESTAMP column less than "today - 1 day" or something similar.  If you want to archive the rows, you will need two queries -- one query to copy the rows to the archival table and another query to delete the archived rows.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.