Bug #90817 FORCE INDEX doesn't take effect when a query has group_by, order_by, and limit
Submitted: 10 May 2018 1:24 Modified: 12 Jun 2018 14:57
Reporter: Shu Lin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.6.40 OS:Any
Assigned to: CPU Architecture:Any

[10 May 2018 1:24] Shu Lin
Description:
FORCE INDEX clause doesn't take effect when a query has group by, order by and limit clauses

How to repeat:
The problem is seen on 5.6.39 and 5.6.40.
5.7.21 doesn't have the issue.

create table t1 (id int not null auto_increment, k int, payload char(100), primary key(id), key idx_k (k));
insert into t1(k) values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
analyze table t1;
explain select * from t1 force index (idx_k) where k between 2 and 5 group by id order by id limit 0,1;

The output of the explain statement is the following. Full table scan is used even though the query force to use the index idx_k.
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | t1    | ALL  | PRIMARY,idx_k | NULL | NULL    | NULL |   10 | Using where; Using filesort |

If we then remove the limit clause, that is:
explain select * from t1 force index (idx_k) where k between 2 and 5 group by id order by id

Then the problem is fixed.
[10 May 2018 8:03] MySQL Verification Team
Thank you for the bug report. Verified as described.
[12 Jun 2018 14:57] Jon Stephens
Documented fix as follows in the MySQL 5.6.41 changelog:

    FORCE INDEX had no effect on a query that used GROUP BY, ORDER
    BY, and LIMIT together.

Closed.