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

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 2

From what I understand about the way that the compiler optimizes these types of queries, using the IN clause is more efficient than multiple OR clauses. If you have values where the BETWEEN clause can be used, that is more efficient still.

Answer 3

I'll bet they are the same, you can run a test by doing the following:

loop over the "in (1,2,3,4)" 500 times and see how long it takes. loop over the "=1 or =2 or=3..." version 500 times and seeing how long it runs.

you could also try a join way, if someField is an index and your table is big it could be faster...

SELECT ... 
    FROM ... 
        INNER JOIN (SELECT 1 as newField UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) dt ON someFIELD =newField

I tried the join method above on my SQL Server and it is nearly the same as the in (1,2,3,4), and they both result in a clustered index seek. I'm not sure how MySQL will handle them.

Answer 4

I bet they will all be the same.

Edit - actually I think the BETWEEN will be faster since it should be converted into:

Field >= 0 AND Field <= 5

It is my understanding that an IN will be converted to a bunch of OR statements anyway. The value of IN is the ease of use. (Saving on having to type each column name multiple times and also makes it easier to use with existing logic - you don't have to worry about AND/OR precedence because the IN is one statement. With a bunch of OR statements, you have to ensure you surround them with parentheses to make sure they are evaluated as one condition.)

The only real answer to your question is PROFILE YOUR QUERIES. Then you will know what works best in your particular situation.

Answer 5

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 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

Answer 7

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