Optimizing the MySQL IN() Comparison Operations Which Include the Indexed Field

The MySQL IN() Comparison Operator is said to be very quick if all the values are constants (the values are then evaluated and sorted first, and the search is done using a binary search).
However, what if the field which the IN clause refers to, is part of the index used to execute the query?
 
For example, lets create a table t with columns a to e

Create table t (
a int unsigned NOT NULL,
b int unsigned NOT NULL,
c int unsigned NOT NULL,
d int unsigned NOT NULL,
e int unsigned NOT NULL,
PRIMARY KEY  (`a`),
KEY `test` (`b`,`c`,`a`));

Now, let’s evaluate the following query:

SELECT a FROM t
FORCE INDEX (test)
WHERE b=4 AND c IN (1,3,5,7,9)
ORDER BY a DESC LIMIT 100;

In order to execute this query MySQL will need to use the ‘test’ index.

At this case mysql will first evaluate a full and unlimited sub query for each value in the IN clause, then, at completion, will merge the results and limit the returned result set.
This full and unlimited evaluation can be very expensive for large tables.
In order to work around this performance caveat we can force a limited sub query evaluation for each value in the IN clause before the merge. This can be done by using a “union all” clause instead of the IN clause, as in the example below.

This will run much faster:

SELECT a FROM (
(SELECT a from t FORCE INDEX (test) where b=4 AND c =1 ORDER BY a DESC LIMIT 100)
UNION ALL
(SELECT a from t FORCE INDEX (test) where b=4 AND c =3 ORDER BY a DESC LIMIT 100)
UNION ALL
(SELECT a from t FORCE INDEX (test) where b=4 AND c =5 ORDER BY a DESC LIMIT 100)
UNION ALL
(SELECT a from t FORCE INDEX (test) where b=4 AND c =7 ORDER BY a DESC LIMIT 100)
UNION ALL
(SELECT a from t FORCE INDEX (test) where b=4 AND c =9 ORDER BY a DESC LIMIT 100)
) temp
ORDER BY a DESC LIMIT 100;

Some notes:
1. For selecting more than a very few fields, it is much faster to select only the primary field and then to join the result with the same table in order to retrieve the rest of the fields (since it is much faster to sort the primary field without the extra selected fields).
2. Substituting the IN clause with OR clauses does not improve the performance of the query.

Thank you Jeffrey Doak for your comments!