MYSQL data archiving

Question

I have a table with a timestamp column that records when the record is modified. I would like to on a nightly basis move all records that are older than 6 days. should I use

insert into archive_table select * from regualr_table where  datediff( now(), audit_updated_date)>=6; 
delete from regular_table where  datediff( now(), audit_updated_date)>=6; 

since there are 1 million rows in regular_table, is there anyway to optimize the query so they run faster? Also will the delete be locking the regular_table? My main concern is the read query to the db won't be slowed down by this archiving process.

Answer 1

Two suggestions:

Compute the value of the cutoff date in a variable, and query compared to that, e.g.:

SET @archivalCutoff = DATE_SUB(NOW(), INTERVAL 6 DAY);

insert into archive_table select * from regular_table where audit_updated_date < @archivalCutoff;

delete from regular_table where audit_updated_date)< @archivalCutoff;

In fact what you have in your question runs into problems, especially with lots of records, because the cutoff moves, you may get records in your regular and archive tables, and you may get records that are deleted but not archived.

The second suggestion is to index the audit_updated field.