CODEWITHSUNDEEP
mysqlcountsql-delete
Ivan
Ivan

Reputation: 2481

Improving a MySQL Delete Query when using COUNT

I found that the following query is causing some bottleneck (the execution time takes more than 40 seconds!)

DELETE FROM doctors 
WHERE (SELECT COUNT(loc_id) 
       FROM locations 
       WHERE locations.loc_doctor = doctors.doc_id) = 0 AND 
       doctors.doc_user = $myVar

I guess the problem lies in the (SELECT COUNT(loc_id) FROM locations WHERE locations.loc_doctor = doctors.doc_id) = 0 section, am I right? Is there a way to improve it?

Upvotes: 0

Views: 185

Answers (3)

tpeczek
tpeczek

Reputation: 24125

This should be a little bit faster:

DELETE FROM doctors WHERE doctors.doc_user = $myVar AND NOT EXISTS (SELECT 1 FROM locations WHERE locations.loc_doctor = doctors.doc_id LIMIT 1)

As your counting for 0 is actually a NOT EXISTS check. You should also consider an index for locations.loc_doctor column (if you don't have one already).

Upvotes: 2

Athiruban
Athiruban

Reputation: 626

I would like to add limit to @tpeczek answer.

DELETE FROM doctors WHERE doctors.doc_user = $myVar AND NOT EXISTS (SELECT * FROM locations WHERE locations.loc_doctor = doctors.doc_id limit 1)

Upvotes: 1

Artyom Skrobov
Artyom Skrobov

Reputation: 311

I'd propose deleting from outer join:

DELETE doctors
FROM doctors LEFT JOIN locations
   ON locations.loc_doctor = doctors.doc_id
WHERE locations.loc_id IS NULL
AND doctors.doc_user = $myVar

Upvotes: 2

Related Questions