Re: WHERE IN() vs. BETWEEN

[Jason Scharlach]

The end query will end up running on 4.1.22 but I've been doing some testing on 5.0.51a in order to get profiling data. I'm seeing similar performance on both versions leading me to believe that there is no significant difference in how the different versions are handling the query.

Here are the explains.
When using BETWEEN()
id: 1
select_type: SIMPLE
table: t1
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 9
ref: NULL
rows: 1678717
Extra: Using where; Using index

When using IN()
id: 1
select_type: SIMPLE
table: t1
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 9
ref: NULL
rows: 73
Extra: Using where; Using index

Note that the only difference between the two is the 'rows:', again pointing to some pre vs. post processing.

As it stands the solution that I've decided to go with is at the application level trying to 'guess' which method would be faster (based on the product of the different IN() clauses) and switching the query I'm using based on that. It's terribly kludgey but I haven't been able to come up with a query that is both fast at low numbers of IN elements as well as being usable at high numbers.

I would love to get any kind of explanation as to what is going on here though.