Deleting from VERY large table

[stew]

Hi,

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....

  DELETE FROM large_table WHERE `date` < NOW() - INTERVAL 2 MONTH

After about an hour staring at

  $ watch 'mysql -Bse "SHOW PROCESSLIST"'

I gave up

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.

Salut

Stew

[r937]

does the date column have an index?

no, your idea to copy what you need into a new table is not flakey

you need not drop the main table, just TRUNCATE it