Which is better for performances: IN or OR

Question

I need to make a query that checks to see if column value is 2117 or 0.

I am currently doing this with OR

select [...] AND (account_id = 2117 OR account_id = 0) AND [...] 

Since I ran into performance issues, I wandered around whether it would be better to do

 select [...] AND account_id IN (0, 2117) AND [...] 

An explanation of the command gives similar results in both cases. So maybe it’s more about optimizing the parsing phase than anything else. Or maybe these two methods are completely equivalent and optimized by mySQL, and I just don't care.

On mySQL, they talk about OR optimization:

Use x = ANY (table containing (1,2)), not x = 1 OR x = 2.

But I did not understand the syntax or even understood why.

What do you think?

Answer 1

There is no competition ... IN always much, much better.

The reason is that databases will not use the index with OR , but will use the index with IN .

Changing OR to IN usually the first optimization I do for queries.

Answer 2

Why not try and run the heavy benchmark? If theres a noticeable difference, then select the best option, otherwise just use "OR" for readability. The source code may provide some useful answers, but this may go beyond performance.

Answer 3

It is usually easier to read and process the engine in it ... However, this speaks on the basis of a limited number. You do not want IN or OR with 20 + ID (usually). When you find yourself in a situation where there are a bunch of numbers, create a table (even a temporary table), insert the values ​​you want to join, and use this as the basis for joining SQL for your results. Offers better flexibility when dealing with large data selections.

Answer 4

I would say that there may be a difference with many elements, but not with two. I would be more likely to tune indexes or look at table architecture to find useful performance improvements.