Strategy Advice for large caching table

[Alan Williamson]

I have been reading a lot of posts here regarding the new partition features and i think i am excited!

We have a database that acts as a caching server -- it basically contains blobs of data that represents HTML snippets.

We keep only the last few days worth of data, dropping the remainder using a rather ugly statement:

delete from LRUCACHE WHERE DATE < DATE_SUB(NOW(),INTERVAL 4 DAY);

now the problem is, this can take over 40mins to complete sometimes. The DATE field is indexed incidentally.

What i would like to do is to utilise partitions and simply "empty" partitions once they move out of the time scope. What would be the best strategy for this?

thanks for any input