Hello and welcome to our community! Is this your first visit?
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Senior Coder
    Join Date
    Dec 2005
    Thanked 77 Times in 77 Posts

    in () versus or...

    Disreagrding less/more readable code in first case, what is better regarding performance ?

    select distinct id_galery  
    from galery_optional_inf 
    where id in (27,28,...)
    select distinct id_galery  
    from galery_optional_inf 
    where id = 27
      or id = 28
      or ...
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Thanked 4,741 Times in 4,703 Posts
    It depends on how good the code optimizer is for the DB you are using.

    If you were using SQL Server or (probably) Oracle, I'd say that you would never see any difference. Both of them have optimizers that are fully capable of converting either of those forms into the other if the optimizer decides one or the other is more efficient.

    With MySQL, my personal bet would be that using IN ( 27, 28, ... ) will be faster.

    MySQL has surprised me on a few occasions with what optimizations it does do, but in general it simply isn't as clever as the big boys. As a person who used to write compilers and interpreters (and hybrids...languages that compiled to a tokenized form and then interpreted the tokens), I can tell you that a non-optimizing compiler or interpreter would still be able to do a pretty efficient job of executing the IN ( ... ) form. Whereas the multiple ORs would simply have to be executed one at a time.

    I remember implementing the code generator for a C compiler and what I did when I was passed a switch ( ) statement. Even in that pretty primitive C compiler (it was for an 8-bit computer! the Zilog Z-80, in 1978) I was smart enough to build a "jump table" and implement an efficient scan thereof. Doing the same thing for a SQL IN ( ... ) would be roughly the same task, and I could make it work pretty darned will, I would think.

    For a definitive answer, as it applies to MySQL, you'd have to write some pretty carefully constructed benchmark tests (to make sure there aren't any side effects that overwhelm the stuff you are trying to test). But if you did it, I'd probably bet you a cup of coffee that IN ( ... ) would win out.
    Last edited by Old Pedant; 11-10-2011 at 08:20 AM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts