Using indexes for "IN" clauses with composite values
It seems that using "IN" clauses with composite values, does not use any of the indexes that might exist for the individual keys.
For example:
SELECT * from foobar where (`field1`,`field2`) IN (('a',1) , ('b',2) ...)
won't use an index for field1, or field2 if they exist.
...so the first question I have is: why is it this way? is it an implementation issue that prevents this? or simply a feature that was not deemed important enough to be done?
What it is very puzzling to me is the fact that even if I have a covering index for all the fields in the "IN" clause...the select will still NOT use the index.
So the same example above:
SELECT * from foobar where (`field1`,`field2`) IN (('a',1) , ('b',2) ...)
.. will not use an index even if I have a composite one with (field1,field2)
Why is that? Is there a way to make this work? ("Force index" doesn't seem to help)
Would it use the indexes if these were part of a composite PK instead of an index?
Cheers,
Josep M.