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?

回答1:

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.

回答2:

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

回答3:

SELECT * FROM tableA

JOIN tableB ON (idA = idB)

WHERE idA <= 100

回答4:

Never use Or if you have In as alternative.

It makes your query super slow