MYSQL query WHERE IN vs OR

Question

I have developed a system using an OR query:

SELECT * FROM tableA 
JOIN tableB ON (idA = idB)
WHERE idA = 1 OR 
      idA = 2 OR 
      idA = 3 OR 
      idA = 4 OR 
      idA = 5 ...... OR 
      idA=100

Compare with query IN:

SELECT * 
FROM tableA JOIN tableB ON (idA = idB)
WHERE idA IN (1,2,3,4,5,......,100)

What is the best query in a MYSQL database?

Possible duplicate of [MYSQL OR vs IN performance](http://stackoverflow.com/questions/782915/mysql-or-vs-in-performance) — Stanley, Dec 20 '12 at 02:17
For things like this, unless you know that this query is a bottleneck in your application, it's not worth the messy logic of multiple `OR` clauses. (Even if it were a little faster.) — Michael Mior, Dec 20 '12 at 04:56
IN() is a syntax shortcut which is (*in syntax terms*) exactly the same as a series of ORs i.e. `id IN(1,2)` is equal to `( id=1 OR id=2 )`. At small scale you will be hard pressed to locate any difference between the two. At very large scale the interpretation time of a long set of ORs will be at a disadvantage. — Paul Maxwell, Oct 22 '17 at 05:20

Bohemian · Accepted Answer · 2012-12-20T02:23:16.997

13

Use IN.

IN will use an index.

OR will (afaik) not use an index.

Also, and this point is not to be sneezed at, the IN version:

uses less code
is easier to maintain
is easier to understand

For those reasons alone I would be prepared suffer a little performance to gain code quality, but you actually gain performance too.

edited Dec 20 '12 at 02:23

answered Dec 20 '12 at 02:18

Bohemian

412,405
93
575
722

4

I know this is years later, but both use of ORs and IN() can (and do) use indexes. As with so many things generalizations are dangerous. In syntax terms IN() is simply a shortcut for a set of ORs. see http://sqlfiddle.com/#!9/c824e2/1 (3 IN queries, 3 OR queries, all 6 use an index; varchar, date, and integer tested) MySQL 5.6 – Paul Maxwell Oct 22 '17 at 07:13

score 4 · Answer 2 · edited May 23 '17 at 12:24

4

In a case like this you should run the queries through EXPLAIN to check how it works against YOUR data. For example:

EXPLAIN SELECT * FROM tableA

JOIN tableB ON (idA = idB)

WHERE idA = 1 OR idA = 2 OR idA = 3 OR idA = 4 OR idA = 5 ...... OR idA=100

This will provide several stats and show whether it's making use of indexes, sequential scans, etc. From that you'll be able to determine what works best for your case.

In general, however, I would go with IN.

Previous work has identified that IN will almost always be faster: MYSQL OR vs IN performance

edited May 23 '17 at 12:24

Community

1
1

answered Dec 20 '12 at 02:20

Ilion

6,772
3
24
47

i get same output using EXPLAIN, i use DESC and get difference value of Showing query time, for OR ( Query took 0.0082 sec ) and IN ( Query took 0.0074 sec ) – hendra Dec 20 '12 at 02:47

score 0 · Answer 3 · answered Dec 20 '12 at 02:20

0

SELECT * FROM tableA

JOIN tableB ON (idA = idB)

WHERE idA <= 100

answered Dec 20 '12 at 02:20

Lee

1
1

hmm, i think's my query not sequence for idA, but idA is random value,, i just create easy example question :) – hendra Dec 20 '12 at 02:48

score 0 · Answer 4 · answered Dec 20 '12 at 02:47

0

Never use Or if you have In as alternative.

It makes your query super slow

answered Dec 20 '12 at 02:47

MoX

93
1
1
7

MYSQL query WHERE IN vs OR

4 Answers4

Linked