basically the same criteria as IN vs EXISTS from
select <select column list> from TABLE_A where col1 in (Select col2 from TABLE_B)
Select <select column list> from TABLE_A where exists (select 1 from Table_B where
Table_B.col2 = Table_A.col1)
where should one use an IN vs the EXISTS clause?
EXISTS works better when:
If the result of
“Select col2 from TABLE_B” is huge (rows in table_b > rows in table_a)
main table TABLE_A is a
relatively small set (table_a rows < table_b rows) AND
executing “select 1 from Table_B where
Table_B.col2 = Table_A.col1″ is very fast because of proper index on
then an exists clause will be better since the optimizer
can do a FTS on main table Table_A and then use the index to do the probe/seek
operations for Table_B.
(essentially a giant Nested Loop)
IN works better when:
If the result of the sub-query (table_b) is small, then the IN clause is much
faster. (the results from the subquery is so small it can be inlined into the main query)
Disclaimer and confidentiality note
This message is confidential and may be privileged. If you are not the intended recipient,
we kindly ask you to please inform the sender. Any unauthorised dissemination or copying
hereof is prohibited. This message serves for information purposes only and shall not
have any legally binding effect. Given that e-mails can easily be subject to
manipulation, we can not accept any liability for the content provided.
> Date: Sun, 29 Mar 2009 09:13:10 +0000
> Subject: Re: IN vs. OR on performance
> From: pookey@stripped
> To: ro4tub@stripped
> CC: mysql@stripped
> 2009/3/29 Oscar <ro4tub@stripped>:
> > Hi all-
> > I want to know what the difference between IN and OR is under the hood.
> > select * from dummy_table where id in (2, 3, 4, 5, 6, 7);
> > select * from dummy_table where id=2 or id=3 or id=4 or id=5 or id=6 or
> > id=7;
> I've have thought once the query is compiled, they are the same. What
> might cause a difference in performance is doing id > 2 and id <= 7.
> Test it on a large dataset and let us know :)
> Blog: http://pookey.co.uk/blog
> Follow me on twitter: http://twitter.com/ipchristian
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
Hotmail® is up to 70% faster. Now good news travels really fast.