MySQL Bugs: #41135: USE INDEX FOR ORDER BY hint has no effect for LIMIT n queries

Bug #41135	USE INDEX FOR ORDER BY hint has no effect for LIMIT n queries
Submitted:	30 Nov 2008 14:10	Modified:	11 Dec 2008 14:11
Reporter:	Sergey Petrunya	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.1, 6.0	OS:	Any
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
"USE INDEX ... FOR ORDER BY" hint doesn't seem to have any effect on ORDER BY ... LIMIT queries.

How to repeat:
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1 (a int, b int, key(a));
insert into t1 select A.a + 10 *(B.a + 10*C.a), A.a + 10 *(B.a + 10*C.a) from t0 A, t0 B, t0 C; 

mysql> explain select * from t1 use index for order by (a)  where a+1 >5 order by a limit 1;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t1    | index | NULL          | a    | 5       | NULL |    1 | Using where | 
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql>  explain select * from t1 use index for order by (a)  where a+1 >5 order by a limit 10;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 1000 | Using where; Using filesort | 
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)

The last two queries differ only in the LIMIT constant. They both have USE INDEX FOR ORDER BY hint. It is apparent that the optimizer makes a cost-based choice and ignores the hint.

Suggested fix:
Make the optimizer always honor the hints over the cost-based considerations.

Thank you for the bug report.

USE INDEX FOR GROUP BY is just telling the optimizer which indexes it *can* use. The optimizer is free to decide whether to use an index or not. 
FORCE INDEX FOR GROUP BY according to our docs (http://dev.mysql.com/doc/refman/5.1/en/index-hints.html) :
"... acts like USE INDEX (index_list) but with the addition that a table scan is assumed to be very expensive. In other words, a table scan is used only if there is no way to use one of the given indexes to find rows in the table."

I've replaced USE INDEX with FORCE INDEX in your example and I'm getting them both to prefer the index over the table scan:
explain select * from t1 force index for order by (a)  where a+1 >5 order by a limit 1
id     1
select_type    SIMPLE
table  t1
type   index
possible_keys  NULL
key    a
key_len        5
ref    NULL
rows   1
Extra  Using where

explain select * from t1 force index for order by (a)  where a+1 >5 order by a limit 10;;
id     1
select_type    SIMPLE
table  t1
type   index
possible_keys  NULL
key    a
key_len        5
ref    NULL
rows   10
Extra  Using where

Agree with Joro's verdict. The described behavior is not a bug.