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
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.6.40
• OS: Any
• 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.