Should I check if rows exist across tables before deleting in MyISAM?

Question

Let's say I have 5 MyISAM tables in my database. Each table has a key, let's call it "id_num" ...

"id_num" is the field which I use to connect all the tables together. A certain value of "id_num" may appear in all tables or sometimes only a subset of the tables.

If I want to delete all instances of a certain "id_num" in the database, can I just make a DELETE command on all tables or should I check to see if that value for "id_num" exists?

DELETE * FROM table1 WHERE id_num = 123;
DELETE * FROM table2 WHERE id_num = 123;
DELETE * FROM table3 WHERE id_num = 123;
DELETE * FROM table4 WHERE id_num = 123;
DELETE * FROM table5 WHERE id_num = 123;

Or should I perform a SELECT command first on each table to check if these rows exist in the table before deletion? What is best practice?

(I am using MyISAM so cascading delete is not an option here.)

use forgein key and cascade it on delete – sandeepKumar Feb 15 '13 at 06:10 — sandeepKumar, Feb 15 '13 at 06:10

score 1 · Answer 1 · edited May 23 '17 at 10:32

Sounds like you need to change your design as follows - have a table with id_num as a PK and make id_num a FK in the other tables, with on-delete-cascade. This will allow you to only run a single delete statement to delete all applicable data (and this is also generally the more correct way of doing things).

The above apparently doesn't work in MyISAM, but there is a workaround using triggers (but now it does seem like a less appealing option).

But I believe your above queries should work, no need to check if something exists first, DELETE will just not do anything.

Most APIs provide you with some sort of rows affected count if you'd like to see whether data was actually deleted.

I don't think you can use foreign keys / on-delete cascade with MyISAM engine ... am I wrong? — Chrysippus, Feb 15 '13 at 07:01

score 1 · Accepted Answer · answered Feb 15 '13 at 07:13

To answer your question about first running SELECT, there's no advantage to doing so. If there's no row in a given table, then the DELETE will simply affect zero rows. If there are matching rows, then doing the SELECT first and then the DELETE would just be doing double the work of finding the rows. So just do the DELETE and get it over with.

Are you aware that MySQL has multi-table DELETE syntax?

If you are certain that table1 has a matching row, you can use outer joins for the others:

DELETE table1.*, table2.*, table3.*, table4.*, table5.*
FROM table1
LEFT OUTER JOIN table2 USING (id_num)
LEFT OUTER JOIN table3 USING (id_num)
LEFT OUTER JOIN table4 USING (id_num)
LEFT OUTER JOIN table5 USING (id_num)
WHERE table1.idnum = 123;

I'm assuming id_num is indexed in all these tables, otherwise doing the JOIN will perform poorly. But doing the DELETE without the aid of an index to find the rows would perform poorly too.

Thank you for pointing out the multi-table DELETE syntax. You anticipated and answered my next question. — Chrysippus, Feb 15 '13 at 21:36

score 0 · Answer 3 · answered Feb 15 '13 at 07:09

0

You should not execute select query before deleting from the table. As select query will put some extra load to the server. However after executing delete query you can check how many rows has been deleted using mysql_affected_rows() function in php.

answered Feb 15 '13 at 07:09

Mohit Mehta

1,283
12
21

Should I check if rows exist across tables before deleting in MyISAM?

3 Answers3