IN(1, 2...n) query optimization

As far as I know, mysql doesn't use index if using IN(1, 2...n) queries. Am I wrong? Or is there anything I could do to make mysql use it? I don't mean IN() subquery optimizatio开发者_Python百科n, because this is clearly explained in the manual.

Example (assuming there is an index on all fields, named index_abc):

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 it doesn't

Thanks in advance for your help.

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

here are described all cases when MySQL can use indexes. i didn't see IN there. rewrite your query to (b = 2 OR b = 4 OR b = 5) AND c = 3 that should make mysql to use index. i think it is just because mysql is not smart enough :)

I agree with Joel.

Try to avoid IN statements where possible. (In most cases they can be replaced by JOINS.) Using IN statements is a known performance drawback. Also be aware that IN statements have a maximum number of allowed members on (most?) databases.

Posting your Explain would really help. Try adding FORCE KEY(keyname) into your query as see if it fixed your issue.