MySQL Bugs: #22950: Innodb does not use primary key for order by

Bug #22950	Innodb does not use primary key for order by
Submitted:	3 Oct 2006 18:54	Modified:	1 Oct 2008 18:23
Reporter:	Peter Zaitsev (Basic Quality Contributor)
Status:	Duplicate
Category:	Server: Optimizer	Severity:	S4 (Feature request)
Version:	5.0, 5.1 BK, 4.1 BK	OS:	Linux (Linux)
Assigned to:	Bugs System	Target Version:
Tags:	Optimizer, innodb
Triage:	D5 (Feature request)

Description:
MySQL Does not use primary key for order by if it is added implicitely.
For Innodb  if id  is primary key,  index (a) is really index on (a,id) internally as for
each a value rows are stored sorted by primary key. 

Heikki has confirmed this is issue which should be fixed.

CREATE TABLE `pktest` (
  `id` int(10) unsigned NOT NULL,
  `a` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `pktest` VALUES (1,1),(2,1),(3,1),(4,2),(5,3),(6,4);

mysql> explain select * from pktest where a=5 order by id;
+----+-------------+--------+------+---------------+------+---------+-------+------+------------------------------------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref   | rows |
Extra                                    |
+----+-------------+--------+------+---------------+------+---------+-------+------+------------------------------------------+
|  1 | SIMPLE      | pktest | ref  | a             | a    | 5       | const |    1 |
Using where; Using index; Using filesort |
+----+-------------+--------+------+---------------+------+---------+-------+------+------------------------------------------+

As you can see filesort is used while it can be avoided.

How to repeat:
see above

Thank you for the report.

Verified on Linux as described using last BK sources.

This is a known problem and will be fixed later, probably in 5.2

This is fixed by BUG#28591 in MySQL 5.0.48.