Searching to maneuver data from the table A to history table B every X days for data that's Y days old after which take away the data from history table B that's over the age of Z days.
Just exploring various ways to do this. So any suggestions could be appreciated.
Example for variables X - 7days Y - 60days z - 365days
CREATE PROCEDURE prc_clean_tables (Y INT, Z INT) BEGIN BEGIN TRANSACTION; DECLARE _now DATETIME; SET _now := NOW(); INSERT INTO b SELECT * FROM a WHERE timestamp < _now - INTERVAL Y DAY; FOR UPDATE; DELETE FROM a WHERE timestamp < _now - INTERVAL Y DAY; DELETE FROM b WHERE timestamp < _now - INTERVAL Z DAY; COMMIT; END
This appears easy.
You'll need a nightly cron job to operate a script.
#crontab -e 50 11 * * * $HOME/scripts/MyWeeklyArchive.sh
The script file is really quite simple too. We'll only use mysqldump and also the Now() function
#! /bin/bash /usr/bin/mysqldump -uUser -pPassword Current_DB Table --where='date < NOW() - INTERVAL 7 DAY' | /usr/bin/mysql -uUser -pPassword archive_DB
You can just bring that line within the cron file, however for scalability and the like I reccomend which makes it a script file.
If you work with MySQL 5.1 you maybe able to utilize the big event scheduler, rather than cron. I haven't tried on the extender but I have tried personally such like in SQL Server.
The easiest method of course is always to place the rows in the online table towards the history table then do your removes. Wrapping that up inside a SPROC as Quassnoi indicates could be perfect.
Another potentially better way is always to make the most of partitioning. For those who have your tables partitioned by date, you need to have the ability to accelerate a minimum of the Remove parts simply by shedding the appropriate partition. The Place part can also be faster since all of the placed rows will come in one partition (should you make your partitions right).
When the schemas are identical, there might be also some type of trick that will permit you to just slowly move the partitions in one table towards the other relatively immediately.