Bug #43341 FORCE INDEX FOR ORDER BY does not affect join queries
Submitted: 3 Mar 2009 21:39 Modified: 3 Mar 2009 21:55
Reporter: Igor Babaev Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.1 OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D3 (Medium)

[3 Mar 2009 21:39] Igor Babaev
Description:
The index hint FORCE INDEX FOR ORDER BY/GROUP BY affects single-table queries, but  is ignored for some join queries with ORDER BY when an index is forced to be used to access rows of the first join table.

How to repeat:
The following test case demonstrates the problem:

create table t1 (a int, b int, index idx(a));
insert into t1 values (5,50), (3,30), (3,33), (7,77), (5,55), (5,51), (3,31);

create table t2 (b int, c int, index idx(b));
insert into t2 values (55,551), (33,333), (55,552), (55,555), (33,337), (55,556), (33,332);
insert into t2 values (50,551), (30,333), (50,552), (50,555), (30,337), (50,556), (30,332);

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

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

mysql> explain select t1.a, t1.b, t2.c from t1,t2 where t1.b=t2.b order by a;
+----+-------------+-------+------+---------------+------+---------+-----------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref       | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+-----------+------+----------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL      |    7 | Using filesort |
|  1 | SIMPLE      | t2    | ref  | idx           | idx  | 5       | test.t1.b |    2 | Using where    |
+----+-------------+-------+------+---------------+------+---------+-----------+------+----------------+
2 rows in set (0.00 sec)

mysql> explain select t1.a, t1.b, t2.c from t1 force index for order by (idx), t2 where t1.b=t2.b order by a;
+----+-------------+-------+------+---------------+------+---------+-----------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref       | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+-----------+------+----------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL      |    7 | Using filesort |
|  1 | SIMPLE      | t2    | ref  | idx           | idx  | 5       | test.t1.b |    2 | Using where    |
+----+-------------+-------+------+---------------+------+---------+-----------+------+----------------+
2 rows in set (0.00 sec)
[3 Mar 2009 21:55] Sveta Smirnova
Thank you for the report.

Verified as described.
[2 Jul 2010 8:13] Jalal Chaer
Any updates?