Which is better; multiple ORs, or a single IN

Databases
1

Perhaps something to add to the Big, Bad Optimization thread:

I’ve tried searching for the answer, but can’t find a good way to give OR and IN enough weight and meaning.

In creating a query based on a user’s selections I end up with something with many, many “OR” clauses. Like so:

select * from my_table where 
my_field = 'apple' or
my_field = 'bottle' or
my_field = 'cat' or
my_field = 'dog' or
my_field = 'elephant' or
my_field = 'fish'
;

But I wonder if using “IN” would be faster or more efficient.

select * from my_table where 
my_field IN ('apple','bottle','cat','dog','elephant','fish')
;

I’ve tried using a bogus table filled with millions of records and got such a wide variance in response times that I doubt my results are meaningful; anywhere from 62 seconds to 130 seconds for both queries.

So one better than the other? Would it matter if searching for strings or numbers?

Thanks,

/Cyberfunkr

2

In theory using IN should be at least as efficient since one side of each comparison is known to be against the same field whereas using OR doesn’t imply that one side of each test is the same field.

In practice as Rudy says, using IN makes the code easier to read and so that makes it the preferred choice even though the performance difference is insignificant.

3

performance is a wash, but the IN list is better because it is more elegant and easier to grok

works for both strings and numbers