MySQL Forums
Forum List  »  Optimizer & Parser

Query Optimizer Problem - OrderBy - 5.0.51 to 5.5.27
Posted by: Dominik Bieringer
Date: September 05, 2012 02:57AM

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 |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+

Options: ReplyQuote


Subject
Views
Written By
Posted
Query Optimizer Problem - OrderBy - 5.0.51 to 5.5.27
2553
September 05, 2012 02:57AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.