MYSQL OR vs IN performance

Question

I am wondering if there is any difference in regards to performance between the following

SELECT ... FROM ... WHERE someFIELD IN(1,2,3,4)

SELECT ... FROM ... WHERE someFIELD between  0 AND 5

SELECT ... FROM ... WHERE someFIELD = 1 OR someFIELD = 2 OR someFIELD = 3 ... 

or will MySQL optimize the SQL in the same way compilers will optimize code ?

EDIT: Changed the AND's to OR's for the reason stated in the comments.

Answer 1

I know that, as long as you have an index on Field, the BETWEEN will use it to quickly find one end, then traverse to the other. This is most efficient.

Every EXPLAIN I've seen shows "IN ( ... )" and " ... OR ..." to be interchangeable and equally (in)efficient. Which you would expect, since the optimizer has no way to know whether or not they comprise an interval. It's also equivalent to a UNION ALL SELECT on the individual values.

Answer 2

I also did a test for future Googlers. Total count of returned results is 7264 out of 10000

SELECT * FROM item WHERE id = 1 OR id = 2 ... id = 10000

This query took 0.1239 seconds

SELECT * FROM item WHERE id IN (1,2,3,...10000)

This query took 0.0433 seconds

IN is 3 times faster than OR

Answer 3

I needed to know this for sure, so I benchmarked both methods. I consistenly found IN to be much faster than using OR.

Do not believe people who give their "opinion", science is all about testing and evidence.

I ran a loop of 1000x the equivalent queries (for consistency, I used sql_no_cache):

IN: 2.34969592094s

OR: 5.83781504631s

Update:
(I don't have the source code for the original test, as it was 6 years ago, though it returns a result in the same range as this test)

In request for some sample code to test this, here is the simplest possible use case. Using Eloquent for syntax simplicity, raw SQL equivalent executes the same.

$t = microtime(true); 
for($i=0; $i<10000; $i++):
$q = DB::table('users')->where('id',1)
    ->orWhere('id',2)
    ->orWhere('id',3)
    ->orWhere('id',4)
    ->orWhere('id',5)
    ->orWhere('id',6)
    ->orWhere('id',7)
    ->orWhere('id',8)
    ->orWhere('id',9)
    ->orWhere('id',10)
    ->orWhere('id',11)
    ->orWhere('id',12)
    ->orWhere('id',13)
    ->orWhere('id',14)
    ->orWhere('id',15)
    ->orWhere('id',16)
    ->orWhere('id',17)
    ->orWhere('id',18)
    ->orWhere('id',19)
    ->orWhere('id',20)->get();
endfor;
$t2 = microtime(true); 
echo $t."\n".$t2."\n".($t2-$t)."\n";

1482080514.3635
1482080517.3713
3.0078368186951

$t = microtime(true); 
for($i=0; $i<10000; $i++): 
$q = DB::table('users')->whereIn('id',[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20])->get(); 
endfor; 
$t2 = microtime(true); 
echo $t."\n".$t2."\n".($t2-$t)."\n";

1482080534.0185
1482080536.178
2.1595389842987

Answer 4

The accepted answer doesn't explain the reason.

Below are quoted from High Performance MySQL, 3rd Edition.

In many database servers, IN() is just a synonym for multiple OR clauses, because the two are logically equivalent. Not so in MySQL, which sorts the values in the IN() list and uses a fast binary search to see whether a value is in the list. This is O(Log n) in the size of the list, whereas an equivalent series of OR clauses is O(n) in the size of the list (i.e., much slower for large lists)

Answer 5

OR will be slowest. Whether IN or BETWEEN is faster will depend on your data, but I'd expect BETWEEN to be faster normally as it can simple take a range from an index (assuming someField is indexed).

Answer 6

It depends on what you are doing; how wide is the range, what is the data type (I know your example uses a numeric data type but your question can also apply to a lot of different data types).

This is an instance where you want to write the query both ways; get it working and then use EXPLAIN to figure out the execution differences.

I'm sure there is a concrete answer to this but this is how I would, practically speaking, figure out the answer for my given question.

This might be of some help: http://forge.mysql.com/wiki/Top10SQLPerformanceTips

Regards,
Frank