deleting big tables??

[Aspen]

Hello all of you,
There are multiple big sized tables in our production DB i.e. 1M+ records in size. As the data in these tables ages, it becomes less relevant to the system. We are looking to employ a simple daily bulk DELETE statement:

DELETE FROM t WHERE time > (now-14 days);
OPTIMIZE t;

We are considering innodb's transactions and locking. Please tell me if this is the best way to do this? Thanks in advance. -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --

[Beau]

I would advice to partition the data, by making the value of "time" part of the table name and dropping the tables as they become old. InnoDB is slow at deleting records if there are many secondary indexes. For inserting to secondary indexes, the insert buffer makes access more sequential. There is no such buffering for deletes. However, DROP TABLE is a very fast operation. MySQL 5.1 supports partitioning, and in MySQL 5.0 one can make use of views to avoid rewriting the application. -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --