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?
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.
You can delete from two tables with one query:
DELETE FROM table1 JOIN table2 ON (join_condition) WHERE table1.expires < UTC_TIMESTAMP() AND table2.expires < UTC_TIMESTAMP
This only makes sense if the JOIN is correct on your data model.
As far as I can tell, there is no reason for the multiple table syntax to be faster. In fact, because of the need to JOIN, it should be a bit slower (not too much, since after being read from disk, the data will already be in memory so only a bit of processing is lost).
The multiple table syntax is not standard SQL compliant. It's a mysql-specific extension.