MySQL DELETE from multiple tables

Question

I need to delete a lot of rows in multiple tables that are older than certain date. Actually I'm doing this:

DELETE FROM trash1 WHERE deletion_date < DATE_SUB(NOW(), INTERVAL 48 HOUR);
DELETE FROM trash2 WHERE deletion_date < DATE_SUB(NOW(), INTERVAL 48 HOUR);
DELETE FROM trash3 WHERE deletion_date < DATE_SUB(NOW(), INTERVAL 48 HOUR);

Is there any approach to make it better or faster? Is it possible to do it in only one query? Thanks.

Answer 1

First thing I would check is the efficiency of the query WHERE deletion_date < DATE_SUB(NOW(), INTERVAL 48 HOUR) on those tables using EXPLAIN.

EXPLAIN DELETE FROM trash1 WHERE deletion_date < DATE_SUB(NOW(), INTERVAL 48 HOUR);

And so on. You're looking to see if the query is using an indexed column or not and how many rows it has to search through. You want to avoid searching every row in the table.

Next thing to check is whether deletion_date is indexed and the type of index. Because this is a range query (ie. "less than") rather than a simple equality check the type of index can matter. There's a discussion about optimizing range queries in the MySQL manual.

Start with that, see how it goes. If you have trouble, post the output of your EXPLAINs.