Query Optimizer Problem - OrderBy - 5.0.51 to 5.5.27

[Dominik Bieringer]

We are facing problems with the execution plan of queries that use an order by. With 5.0.51 an existing index was used, with 5.5 it is not used anymore. I created a very small example that shows the problem.

Why the index is not used anymore with 5.5? When i use "force index (testIndex)" it works.


With 5.0.51

create database test;
use test;
create table test (id int primary key, a varchar(20), b varchar(30)) engine = innodb;
explain select * from test order by a;
create index testIndex on test(a);
explain select * from test order by a;

--> Results in:

+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 1 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+



+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------+
| 1 | SIMPLE | test | index | NULL | testIndex | 63 | NULL | 1 | |
+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------+




============================



With 5.5.27

create database test;
use test;
create table test (id int primary key, a varchar(20), b varchar(30)) engine = innodb;
explain select * from test order by a;
create index testIndex on test(a);
explain select * from test order by a;

--> Results in:


+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 1 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+


+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 1 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+