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 16:54	Modified:	1 Oct 2008 16:23
Reporter:	Peter Zaitsev (Basic Quality Contributor)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S4 (Feature request)
Version:	5.0, 5.1 BK, 4.1 BK	OS:	Linux (Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	innodb, Optimizer

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.