Re: IN vs. OR on performance

[Baron Schwartz]

On Sun, Mar 29, 2009 at 10:19 AM, Claudio Nanni  wrote:
> An explain of the two statements yields the same plan,
> anybody knows if they are actually translated in the same plan?

There is a difference.  The IN list is sorted so lookups can be done
as a binary search.  A bunch of OR's just evaluates each condition one
at a time until one matches.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: IN vs. OR on performance

[Claudio Nanni]

An explain of the two statements yields the same plan,
anybody knows if they are actually translated in the same plan?

Claudio

Ian P. Christian wrote:

2009/3/29 Oscar :
  

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 :)

  



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: IN vs. OR on performance

[Martin Gainty]

basically the same criteria as IN vs EXISTS from 
http://decipherinfosys.wordpress.com/2007/01/30/in-vs-exists/

select  from TABLE_A where col1 in (Select col2 from 
TABLE_B)
VS
Select  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 +
> 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 :
> > 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

Re: IN vs. OR on performance

[Ian P. Christian]

2009/3/29 Oscar :
> 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=arch...@jab.org

IN vs. OR on performance

[Oscar]

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 run the query sql on the test schema, it seems that there is no
performance difference between these two pieces of sql.

Thank you,

-Oscar