Strange use of the index in Mysql

Question

Explain
SELECT `feed_objects`.*
FROM `feed_objects`
WHERE (`feed_objects`.feed_id IN
  ( 165,160,159,158,157,153,152,151,150,149,148,147,129,128,127,126,125,124,122,
    121, 120,119,118,117,116,115,114,113,111,110)) ;

+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table        | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | feed_objects | ALL  | by_feed_id    | NULL | NULL    | NULL |  188 | Using where |
+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+

Not used index by_feed_id

---

But when I point less than the values in the WHERE - everything is working right

Explain
SELECT `feed_objects`.*
FROM `feed_objects`
WHERE (`feed_objects`.feed_id IN
  (165,160,159,158,157,153,152,151,150,149,148,147,129,128,127,125,124)) ;

+----+-------------+--------------+-------+---------------+------------+---------+------+------+-------------+
| id | select_type | table        | type  | possible_keys | key        | key_len | ref  | rows | Extra       |
+----+-------------+--------------+-------+---------------+------------+---------+------+------+-------------+
|  1 | SIMPLE      | feed_objects | range | by_feed_id    | by_feed_id | 9       | NULL |   18 | Using where |
+----+-------------+--------------+-------+---------------+------------+---------+------+------+-------------+

Used index by_feed_id

---

What is the problem?

Answer 1

The MySQL optimizer makes a lot of decisions that sometimes look strange. In this particular case, I believe that you have a very small table (188 rows total from the looks of the first EXPLAIN), and that is affecting the optimizer's decision.

The "How MySQL Uses Indexes" manual pages offers this snippet of info:

Sometimes MySQL does not use an index, even if one is available. One circumstance under which this occurs is when the optimizer estimates that using the index would require MySQL to access a very large percentage of the rows in the table. (In this case, a table scan is likely to be much faster because it requires fewer seeks.)

Because the number of ids in your first WHERE clause is relatively large compared to the table size, MySQL has determined that it is faster to scan the data than to consult the index, as the data scan is likely to result in less disk access time.

You could test this by adding rows to the table and re-running the first EXPLAIN query. At a certain point, MySQL will start using the index for it as well as the second query.