‘EXISTS’ is better than ‘IN’

While working on my last project, I was facing a query which was taking 30 second to execute. The number of tables involved were 10 and total records were around 20k.  It was a search functionality for a web-app project.  There were lot of sub-queries involved in the first draft, all of them using ‘IN’. After doing some research ( within MySQL Community ) , I just replaced all ‘IN’  by ‘EXISTS’ and voila !  The query took 11 seconds to execute.

For example, you could change this query  :

SELECT *

FROM invitees i

WHERE i.event_id IN ( SELECT e.id

FROM event e

WHERE e.city LIKE = ‘ABC’ )

TO

SELECT *

FROM invitees i

WHERE EXISTS ( SELECT 1

FROM event e

WHERE e.city LIKE = ‘ABC’

AND e.id = i.event_id ).

Try it and let me know if you find the same difference in performance as I have. There were many more modifications  to the original query to bring it further down to 1 sec. But I will talk about that later.  For more information on  EXISTS and IN Sub Queries see Reference below.

References :

http://dev.mysql.com/doc/refman/5.0/en/optimizing-subqueries.html

http://dev.mysql.com/doc/refman/5.0/en/in-subquery-optimization.html

[Permalink](the_permalink(); ?>)