Deleting from VERY large table
I have a huge table, almost 1GB and I have inherited the job of trimming it a bit by removing all the records that are over 2 months old.
Easy I hear you all say! Thats what I thought....
After about an hour staring at
DELETE FROM large_table WHERE `date` < NOW() - INTERVAL 2 MONTH
I gave up :(
$ watch 'mysql -Bse "SHOW PROCESSLIST"'
One way I thought of doing it was selecting the records I want to keep into a temporary table dropping the main table, recreating it and putting the records I want to keep back in.
This sounds a bit flaky to me so I was wondering if any of you guys had any ideas.
The table type is InnoDB if this helps.