View Full Version : in () versus or...

11-10-2011, 07:52 AM
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 ...

Old Pedant
11-10-2011, 08:12 AM
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.