CODEWITHSUNDEEP
mysqlsqlwhere-in
hendra
hendra

Reputation: 211

MYSQL query WHERE IN vs OR

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?

Upvotes: 4

Views: 8707

Answers (4)

MoX
MoX

Reputation: 93

Never use Or if you have In as alternative.

It makes your query super slow

Upvotes: 0

Bohemian
Bohemian

Reputation: 425073

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.

Upvotes: 13

Lee
Lee

Reputation: 1

SELECT * FROM tableA

JOIN tableB ON (idA = idB)

WHERE idA <= 100

Upvotes: 0

Ilion
Ilion

Reputation: 6872

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

Upvotes: 4

Related Questions