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.