Bug #43342 FORCE INDEX hint does not differentiate between ORDER BY and GROUP BY

• Submitted: 3 Mar 2009 21:54
• Modified: 3 Mar 2009 22:05
• Reporter: Igor Babaev
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 5.1, 6.0
• OS: Any
• CPU Architecture: Any

[3 Mar 2009 21:54] Igor Babaev

Description:
The FORCE INDEX FOR GROUP BY hint forces to use the specified index for queries without any GROUP BY: 

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 * from t1 force index for group 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)

How to repeat:
Run the following sequence of commands to see 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);
explain select * from t1 order by a;
explain select * from t1 force index for order by (idx) order by a;
explain select * from t1 force index for group by (idx) order by a;

[3 Mar 2009 22:05] Sveta Smirnova

Thank you for the report.

Verified as described.

"force index for order by" forces index for query with group by and without order by as well.