SQL filtering by multiple columns with indexes

I'd like to expand a question I posted a while ago:

I'm querying a table for rows in which pairs of columns are in a specific set. For example, consider the following table:

    id | f1  | f2
    -------------
    1  | 'a' | 20
    2  | 'b' | 20
    3  | 'a' | 30
    4  | 'b' | 20
    5  | 'c' | 20

And I wish to extract rows in which the pair (f1, f2) are in a specified set of pairs, e.g. (('a',30), ('b', 20),...). In the original question, Mark answered correctly that I can use the following syntax:

SELECT * FROM my_table WHERE (f1,f2) IN (('a',30), ('b',20))

This works fine, but I see some unexpected behavior regarding indexes:

I've defined a multi-column index for f1,开发者_如何学C f2, named IndexF1F2. Using the EXPLAIN phrase, I see that MySql uses the index for a single comparison, e.g.:

SELECT * FROM my_table WHERE (f1,f2) = ('a',30)

but not when using the 'IN' clause, as in the example above. Giving hints, e.g. USE INDEX(IndexF1F2) or even FORCE INDEX(IndexF1F2), does not seem to make any difference.

Any ideas?

---

This is a known bug in MySQL.

Use this syntax:

SELECT  *
FROM    composite
WHERE   (f1, f2) = ('a', 30)
        OR (f1, f2) = ('b', 20)