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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
Julian HansenCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.