Bug #87670 Force index for group by is not always honored
Submitted: 5 Sep 2017 9:32 Modified: 6 Sep 2017 7:52
Reporter: Arnaud Adant Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.7.18, 5.6.37, 5.7.19 OS:Any
Assigned to: CPU Architecture:Any
Tags: hints

[5 Sep 2017 9:32] Arnaud Adant
Description:
The force index for group by is not honored by the optimizer while it could be quicker to use the index for group by.

MySQL [test]> explain select id, max(ts) from t group by id;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | index | PRIMARY       | PRIMARY | 8       | NULL | 32401 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

MySQL [test]> explain select id, max(ts) from t force index for group by (primary) group by id;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | index | PRIMARY       | PRIMARY | 8       | NULL | 32401 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

How to repeat:
A bit random :

drop table if exists t;
create table t(id int auto_increment, ts timestamp not null, primary key(id,ts)) engine=InnoDB, stats_sample_pages = 20;
insert into t(ts) values ('2017-01-01');
insert into t(ts) values ('2017-01-01');
replace into t(ts) select t1.ts from t t1, t t2, t t3, t t4, t t5, t t6, t t7, t t8, t t9, t t10, t t11, t t12, t t13, t t14, t t15;
insert into t(id,ts) select distinct id, '2017-01-02' from t;
select count(distinct id) from t;
analyze table t;
show indexes from t;
explain select id, max(ts) from t group by id;
explain select id, max(ts) from t force index for group by (primary) group by id;

Repeatable :

drop table if exists t;
create table t(id int auto_increment, ts timestamp not null, primary key(id,ts)) engine=InnoDB, stats_sample_pages = 20;
insert into t(ts) values ('2017-01-01');
insert into t(ts) values ('2017-01-01');
replace into t(ts) select t1.ts from t t1, t t2, t t3, t t4, t t5, t t6, t t7, t t8, t t9, t t10, t t11, t t12, t t13, t t14, t t15;
select count(distinct id) from t;
analyze table t;
show indexes from t;
explain select id, max(ts) from t group by id;
explain select id, max(ts) from t force index for group by (primary) group by id;

Suggested fix:
Allow force index for group by to use the index for group by.
[6 Sep 2017 7:52] MySQL Verification Team
Hello Arnaud,

Thank you for the report.
Observed this with 5.7.19/5.6.37 (5.5.56 - shows confirms Using index for group-by without much efforts though).

Thanks,
Umesh