too long

Question

I have 2 MySQL tables having different amount and types of columns. But there's one column of type DATETIME that is called the same in both: expires.

I'm now using 2 queries:

"DELETE FROM `table1` WHERE `expires` < UTC_TIMESTAMP()"
"DELETE FROM `table2` WHERE `expires` < UTC_TIMESTAMP()"

Q1: I have never used the DELETE query for multiple tables that have no connections so I couldn't figure out the right way to create a single query. Is it possible at all without using COMMIT?

Q2: I don't have many records in the DB yet so I can't test the performance difference between using 2 queries from PHP or using a single one to delete the expired records. Does anybody know it?

Answer 1

If you are concerning about time issues for example, the first query will take 1 minute to execute but this will be a problem for your system (the second table values will expire 1 minute + second query execution time later), you might use them together with a transaction like:

begin transaction
set @ts = utc_timestamp();
delete from table_1 where expires< @ts;
delete from table_2 where expires< @ts;
commit

and they will be both updated at the same time.