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?
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?
Ray Paseur
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.