how to optimize SQL queries using IN having indexed keys

Question

I am running this query

EXPLAIN SELECT id, timestamp from foo where id IN (23,67,78,90) order by ASC

here id is indexed. But then too when I am running Explain I am getting this in Using where;Using Index in Extra

+----+-------------+---------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+---------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | foo   | range | PRIMARY       | PRIMARY | 8       | NULL |   12  | Using where;Using Index|
+----+-------------+---------------+-------+---------------+---------+---------+------+------+-------------+

But when I am running this same query with single id nothing is in Extra its working as expected in the case of index

EXPLAIN SELECT id, timestamp from foo where id = 23`

+----+-------------+---------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table| type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+---------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | foo | range | PRIMARY       | PRIMARY | 8       | NULL |  1 |        |
+----+-------------+---------------+-------+---------------+---------+---------+------+------+-------------+

I think something wrong with IN. Can anyone tell me the way to optimize it ?

Answer 1

As per my knowledge,

IN query will take more time than Single "=". If "IN" have single value then it is equal to single "=" query.

Because it is MULTIPLE OR conditions of "=" OR "=".

id will match with every 4 values in "IN" array.

Only way to optimise is to have index.

Update:

If the Extra column also says Using where, it means the index is being used to perform lookups of key values. Without Using where, the optimizer may be reading the index to avoid reading data rows but not using it for lookups. For example, if the index is a covering index for the query, the optimizer may scan it without using it for lookups. See explanation