Query optimization

Question

Is there any way to optimise this query to something quicker?

SELECT id FROM business 
WHERE id NOT IN(SELECT business_id FROM business_community GROUP BY business_id)

I am not sure if a left join followed by a `NULL` check would be faster, but I think it's worth trying. — lijie, Dec 10 '10 at 14:25
Just for reference, it is a bug in mysql -> http://stackoverflow.com/questions/3417074/why-would-an-in-condition-be-slower-than-in-sql — Itay Moav -Malimovka, Dec 10 '10 at 14:37

score 5 · Accepted Answer · answered Dec 10 '10 at 14:26

5

Try this:

SELECT id FROM business AS b
LEFT JOIN business_community bc ON bc.business_id = b.id
WHERE bc.business_id IS NULL

answered Dec 10 '10 at 14:26

Marek Kwiendacz

score 0 · Answer 2 · answered Dec 10 '10 at 14:34

0

Maybe using "DISTINCT business_id" rather than a group by..

answered Dec 10 '10 at 14:34

kgautron

2 Answers2