Bug #45001 EXPLAIN says "Using index; Using filesort", however table scan is used
Submitted: 21 May 2009 6:22 Modified: 6 Jul 2009 13:31
Reporter: Yuan WANG Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.1.33, 4.1, 5.0, 5.1, 6.0 bzr OS:Any
Assigned to: CPU Architecture:Any

[21 May 2009 6:22] Yuan WANG
Description:
If an ORDER BY is done via filesort and all used columns is in an index. Then EXPLAIN will say "Using index; Using filesort", however table scan is used actrually.

How to repeat:
create table t(a int primary key, b int, c int, key bc(b, c)) engine = innodb;
insert into t values(1, 1, 1);
insert into t values(2, 2, 1);
insert into t values(3, 1, 2);

mysql> explain select b from t order by c;
+----+-------------+-------+-------+---------------+------+---------+------+----
--+-----------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | row
s | Extra                       |
+----+-------------+-------+-------+---------------+------+---------+------+----
--+-----------------------------+
|  1 | SIMPLE      | t     | index | NULL          | bc   | 10      | NULL |
3 | Using index; Using filesort |
+----+-------------+-------+-------+---------------+------+---------+------+----
--+-----------------------------+

EXPLAIN says that index bc will be used. However after debuging you can find that table scan is used actually.

Suggested fix:
In this case, an index scan on bc should be enough.
[21 May 2009 8:20] Sveta Smirnova
Thank you for the report.

> However after debuging you can find that table
scan is used actually.

Please provide what exactly did you do: gdb output.
[22 May 2009 1:33] Yuan WANG
Sorry that I don't use gdb. I use MS VC++ in Windows. However to confirm this is easy. Just set two breakpoints in ha_innobase::rnd_next and ha_innobase::index_next. And you will find that ha_innobase::rnd_next will be hit and ha_innobase::index_next won't.
[25 May 2009 10:09] Sveta Smirnova
Thank you for the feedback.

Verified as described.

Problem repeatable with MyISAM as well: just replace ha_innodb with ha_myisam
[27 May 2009 15:42] Sveta Smirnova
Omer,

you are right: problem is not repeatable with more rows.
[6 Jul 2009 13:31] Sergei Golubchik
So, I presume, it's a [not very serious] bug in explain.