All geek questions in one place

Query Optimization IN (1, 2 ... n)

As far as I know, mysql does not use index when using IN (1, 2 ... n) queries. Am I mistaken? Or can I do something to make mysql use it? I do not mean optimizing the IN () subquery, as this is clearly explained in the manual.

Example (assuming index_abc is specified for all fields):

WHERE a = 1 AND b = 2 AND c = 3 - then it uses index_abc

WHERE a = 2 AND b IN (2, 4, 5) AND C = 3 - then this is not

Thanks in advance for your help.

+4
source share
4 answers

What determines where the values ​​in the IN expression come from? Odds are either user input or something that should be somewhere in the table. Examples of things that should be in the table include hard-coded search values. Even user input can be inserted into the table first. Then you can use a JOIN rather than IN , and your indexes will work fine.

+3
source

all cases where MySQL may use indexes are described here . I have not seen there. rewrite your query to (b = 2 OR b = 4 OR b = 5) AND c = 3 , which should make mysql use the index. I think this is simply because mysql is not smart enough :)

+2
source

I agree with Joel.

Avoid IN instructions whenever possible. (In most cases, they can be replaced by JOINS.) Using IN statements is a known performance flaw. Also keep in mind that IN statements have the maximum number of allowed members in (most?) Databases.

+1
source

Posting your explanation will really help. Try adding FORCE KEY (key name) to your query to see if your problem has been fixed.

0
source

Source: https://habr.com/ru/post/1302994/

More articles:


All Articles