Mysql – Deleting multiple tables in a single delete query in Mysql

deleteMySQLperformancequery-performance

I have a task to delete records from multiple tables (say 4 or more). I came to know that I can do the delete in a single MySQL query as follows

DELETE table1,table2,table3,table4 
  FROM table1 
  LEFT JOIN table2 ON table1.pk=table2.pk 
  LEFT JOIN table3 ON table3.pk=table2.pk 
  LEFT JOIN table4 ON table4.pk=table3.pk 
 WHERE table1.pk IN (101,102,106,...)

How does it works internally? Any other performance caveats compared to spliting the query into 4 separate delete queries?

Best Answer

How does it works internally?

I assume that it performs the JOINs as if you were SELECTing data out of those tables, nothing which rows matched, and then removing them.

Any other performance caveats compared to spliting the query into 4 separate delete queries?

That depends on what you intend. If you are otherwise doing:

DELETE FROM table1 WHERE pk IN (101,102,106,...)
DELETE FROM table2 WHERE pk IN (101,102,106,...)
DELETE FROM table3 WHERE pk IN (101,102,106,...)
DELETE FROM table4 WHERE pk IN (101,102,106,...)

the joining of data may well make the process slower. If you need to join to only delete rows that exist in all four table (see below) then this will be faster as that operation only needs to be done once.

Any other performance caveats compared to spliting the query into 4 separate delete queries?

A very important consideration here is not standard SQL syntax that will be usable elsewhere. It will limit portability and may confuse future maintainers of the code, so I would not recommend it.

Also, it may not be doing what you actually intend. The JOINs there will enforce only deleting rows that match in the first table, so if pk=102 exists in tables 2, 3, and 4 but not in 1, it will not be deleted from the three it does exist in. Unless your sample query is incorrect: if table 1 contains parent entities for entities in the other tables, and so forth, therefore you are matching a foreign key to a primary key, like so:

DELETE table1,table2,table3,table4 
  FROM table1 
  LEFT JOIN table2 ON table1.pk=table2.fkReferecingTable1 
  LEFT JOIN table3 ON table3.pk=table2.fkReferecingTable1 
  LEFT JOIN table4 ON table4.pk=table3.fkReferecingTable1 
WHERE table1.pk IN (101,102,106,...)

If this is the case then you might want to consider defining the foreign keys with ON DELETE CASCADE in which case you could simply execute

DELETE table1 FROM table1 WHERE table1.pk IN (101,102,106,...)

and let the database engine handle the rest. I say "might want to consider" because IMO ON DELETE CASCADE can be rather dangerous, so I usually avoid it unless it is the only efficient way.

Related Question