List:General Discussion« Previous MessageNext Message »
From:Martin Gainty Date:March 29 2009 1:07pm
Subject:RE: IN vs. OR on performance
View as plain text  
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
the sub-query 
“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:
> Follow me on twitter:
> -- 
> MySQL General Mailing List
> For list archives:
> To unsubscribe:

Hotmail® is up to 70% faster. Now good news travels really fast.
IN vs. OR on performanceOscar29 Mar
  • Re: IN vs. OR on performanceIan P. Christian29 Mar
    • RE: IN vs. OR on performanceMartin Gainty29 Mar
    • Re: IN vs. OR on performanceClaudio Nanni29 Mar
      • Re: IN vs. OR on performanceBaron Schwartz31 Mar