1

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?

6
  • I don't' think you can do that , you to write separate DELETE statements for each table
    – Biju jose
    Mar 25, 2019 at 7:32
  • is it delete from all 4 tables?
    – Biju jose
    Mar 25, 2019 at 7:34
  • yeah that works I think in mysql
    – Biju jose
    Mar 25, 2019 at 7:36
  • 2
    How does it works internally? Any other performance caveats compared to spliting the query into 4 separate delete queries? Very doubtful. Most likely, a similar SELECT is performed first, which selects/marks those table records that should be deleted, and then all selected/marked records are deleted.
    – Akina
    Mar 25, 2019 at 7:58
  • Modern MySQL versions can do EXPLAIN DELETE .... Compare this to EXPLAIN SELECT ..
    – danblack
    Mar 25, 2019 at 11:57

1 Answer 1

2

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.

1
  • @ypercubeᵀᴹ - good catch, not sure where my brain went there. Will update accordingly. Mar 25, 2019 at 16:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.