How to optimize this query?

Question

Query:

select id,
       title 
  from posts 
 where id in (23,24,60,19,21,32,43,49,9,11,17,34,37,39,46,5
2,55)

Explain plan:

mysql> explain select id,title from posts where id in (23,24,60,19,21,32,43,49,9,11,17,34,37,39,46,5
2,55);
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | posts | ALL  | PRIMARY       | NULL | NULL    | NULL |   30 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.05 sec)

id is the primary key of posts table.

Answer 1

Other than adding other indexes, such as

• a clustered index id
• a covering index which includes id [first] and the other columns from the SELECT clause

there seem to be little to be done...

In fact, even if there were such indexes available, MySQL may decide to do a table scan, as is the case here ("ALL" type). The reason may be the table may has a relative few rows (compared with the estimated number of rows the query would return), and it is therefore more efficient to "read" the table, sequentially, discarding non matching rows as we go, rather than "hoping all over the place", with an index indirection.

Answer 2

I don't see any problem with it. If you need to select against a list, then "IN" is the right way to do it. You're not selecting unnecessary information, and the thing you're selecting against is a key, which is presumably indexed.