Bug #18458 ORDER BY (a,b) LIMIT ... should be able to utilize single-column index on (a)
Submitted: 23 Mar 2006 14:57 Modified: 18 Aug 2007 2:15
Reporter: Domas Mituzas Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:4.1,5.0,5.1 OS:Linux (Linux)
Assigned to: Assigned Account CPU Architecture:Any

[23 Mar 2006 14:57] Domas Mituzas 
Description:
In such situation:

CREATE TABLE t1 ( a INT, b INT, c INT,
KEY (a), KEY (b), KEY (c));

A query: 
SELECT .. FROM t1 ORDER BY a,b LIMIT x 

should use index on (a) to fetch topmost x rows, read the value of x'th 'a', and continue scanning index, until 'a' changes. 

This way filesort could be done on significantly reduced dataset.

Additionally, rows with unique a values could bypass filesort (instead of that second pass on same index merged against filesorted data could be done).  

How to repeat:
mysql> select * from inno order by b,a limit 1;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 | 1    | 1    |
+---+------+------+
1 row in set (0.62 sec)

Suggested fix:
implement suggested optimizations
[18 Aug 2007 2:15] Igor Babaev 
- This is a request for an optimization that does not look to be widely applied.
- Yet an implementation of this feature will take 2 man-months.

By the above reasons I move the bug to 'To be fixed later'.
Product management will decide in what version a fix for this problem appears.