Use IN or multiple OR? Which one performs better?

Asked

Viewed 339 times

4

I have the following queries in Mysql:

1º: Using multiple devices OR

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

2º: Using IN

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

Which query will have better performance? Both will result me the same data or has difference?

mysql sql query performance
  • 3

    The following: (requires translation) https://stackoverflow.com/questions/782915/mysql-or-vs-in-performance

  • Very good, thank you.

1 answer

4


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

But in general the choice should be based on semantics and not by performance. The IN search for a value in a data set, the OR concatenates conditions and is only worth the use if it is in small quantity. For small quantity the performance will not make a difference. For large volume of comparison OR is virtually unviable.

The IN is more powerful by being able to buy with a data set to be determined.

When it makes no difference which to use, choose a shape and always be consistent.

Browser other questions tagged mysql sql query performance

You are not signed in. Login or sign up in order to post.