basically the same criteria as IN vs EXISTS from
http://decipherinfosys.wordpress.com/2007/01/30/in-vs-exists/
select <select column list> from TABLE_A where col1 in (Select col2 from
TABLE_B)
VS
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)
AND
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
Table_B.col2,
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)
http://decipherinfosys.wordpress.com/2007/01/30/in-vs-exists/
HTH
Martin
______________________________________________
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: poo...@pookey.co.uk
> To: ro4...@gmail.com
> CC: mysql@lists.mysql.com
>
> 2009/3/29 Oscar <ro4...@gmail.com>:
> > 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=mgai...@hotmail.com
>
_________________________________________________________________
Hotmail® is up to 70% faster. Now good news travels really fast.
http://windowslive.com/online/hotmail?ocid=TXT_TAGLM_WL_HM_70faster_032009
