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?