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.
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.