I have a table:
create table user (
id int primary key,
name varchar(20)
)engine=innodb;
insert into user values(1,'lily');
insert into user values(2,'tom');
insert into user values(3,'bob');
insert into user values(4,'jimi');
insert into user values(5,'moth');
If I run
explain select * from user where id in (1,2,3)
or
explain select name from user where id in (1,2,3)
Mysql shows that the type is range, but when run:
explain select id from user where id in (1,2,3)
Mysql shows that the type is index. So why is there such a difference?
The critical thing to realize here is that for such a small table MySQL may not choose to use any index, despite there being an index which might help the query plan execute faster. Actually, both of the queries you showed should be sargable, and MySQL should be able to use the clustered index on the id primary key. Try adding substantially more data, and MySQL would likely use an index scan to satisfy the query.