CODEWITHSUNDEEP
mysqlsql
lyhong
lyhong

Reputation: 947

Mysql - delete with join vs two delete statements

I have two tables with related key. I want to choose the best way to delete row from tbl_one and tbl_two rows that have related key. I tried using DELETE JOIN to do this correctly, but I found another way that is very simple that I use two statements of delete. Could you tell me which is better?

First method:

DELETE tbl_one,
tbl_two FROM tbl_one 
JOIN tbl_two ON tbl_one.id = tbl_two.tbl_one_id WHERE tbl_one.id = 1

Second method:

DELETE FROM tbl_one WHERE id =1;
DELETE FROM tbl_two WHERE tbl_one_id =1;

Upvotes: 0

Views: 81

Answers (2)

avisheks
avisheks

Reputation: 1180

The main point of concern the operation should be done in isolation(either both or none) you should put the operations inside transaction block. In my perspective first query works better just because the server can reach the savepoint with a single query rather than parsing and executing two.

turn off the foreign_key_check global variable and run the query and turn it on back afterwards.

NB: You can get use of cascading foreign key behavior mysql provides.

Upvotes: 1

arkascha
arkascha

Reputation: 42935

It does not matter if you use a single or multiple statements to alter database content, as long as you are using transactions. Without transactions two issues might arise:

  1. another process accessing the data inbetween you running one statement after another queries a state of the database that is "unclean", because only part of the statements has been processed. This may always happen in a system where more than a single client can use the database at the same time, for example in web pages and the like.

  2. a subsequent query might fail, out of whatever reason. In that case only part of your statements have been processed, the other part not. That leaves your database in an "undefined" state again, a persistent situation in this case. You'd have to manually prevent this by error detection, but even then it might simply not be possible to fix the issue.

Relational database management systems offer transactions for this. Transactions allow to "bundle" several statements to a single one from a logical point of view. You start a transaction, run your statements, then close the transaction. If something unexpected occurred you can always "rollback" your transaction, that way you get a stable and clean database situation just like before the start of your transaction.

Upvotes: 0

Related Questions