Why Mysql explain use index for following SQL statement instead of range

user_13803104 0
Viewed 55

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?

mysql
1 Answers

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.

user_18632290
Related