Use IN or multiple ORs? Which one has the best performance?

Question

Use IN or multiple ORs? Which one has the best performance?

Navigation

#1 by (4 votes)

4

I have the following queries in MySQL:

1st: Using multiple OR

 SELECT SUM(qtd)
    FROM 
        produtos
    WHERE 
        qtd > 10 
        and (status = '0' or status = '4' or status = '7')

2nd: Using IN

 SELECT SUM(qtd)
    FROM 
        produtos
    WHERE 
        qtd > 10 
        and status IN (0,4,7)

Which query will perform best? Will both of these be the same data or difference?

mysql sql desempenho query

asked by anonymous 06.07.2017 / 15:45

1 answer

Redeem all nodes in a SQLite database tree interpret account in string C #

score 4 · Accepted Answer

IN is an internal implementation and can be optimized better. It should not have big gain, but it's to be faster for a large comparison list.

But in general the choice must be in terms of semantics, not performance. The IN looks for a value in a data set, the OR concatenates conditions and is only worth the use if it is a small amount. For small amounts the performance will not make any difference. For large comparison volume OR is virtually unfeasible.

IN is more powerful to be able to buy with a set of data to be determined.

When it does not matter what to use, choose a shape and always be consistent.