MySQL Bugs: #103039: Query with group by not showing that it is using the index for the group by

Bug #103039	Query with group by not showing that it is using the index for the group by
Submitted:	18 Mar 2021 14:49	Modified:	26 Mar 2021 2:03
Reporter:	John Carew	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S5 (Performance)
Version:	8.0.23, 5.7.33	OS:	CentOS (8.2.2004)
Assigned to:		CPU Architecture:	Any

Description:
Using the basic example given in the MySql docs, we are not seeing the query plan showing that it is using the index for the group by. The docs say that it should be showing 'Using index for group-by' in the extra column from the explain, it only shows 'Using index'.
https://dev.mysql.com/doc/refman/8.0/en/group-by-optimization.html#loose-index-scan

How to repeat:
CREATE TABLE t1 (
    id int PRIMARY KEY AUTO_INCREMENT,
    c1 nvarchar(10),
    c2 nvarchar(10),
    c3 nvarchar(10),
    c4 nvarchar(10),
    INDEX idx(c1,c2,c3)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO t1 (c1, c2, c3, c4) VALUES ('1','1','0','1'),('2','3','1','2'),('10','4','10','10'),('0','4','100','0'),('3','4','100','3'),('5','5','9','5'),('8','7','3','8'),('7','7','4','7'),('30','8','15','30');

EXPLAIN SELECT c1, c2 FROM t1 GROUP BY c1, c2;
# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 't1', NULL, 'index', 'idx', 'idx', '99', NULL, '18', '100.00', 'Using index'

EXPLAIN SELECT c1, c2 FROM t1 WHERE c1 < 5 GROUP BY c1, c2;
# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 't1', NULL, 'index', 'idx', 'idx', '99', NULL, '18', '33.33', 'Using where; Using index'

SELECT @@VERSION;
# @@VERSION
'8.0.23'

Hello John Carew,

Thank you for the report and test case.

regards,
Umesh

Just to be clear, we are seeing this behavior with one of our small queries against a very large table(13 mil). That is what brought us to try the simple example in the docs, to see if we could get the group by to use the index in the example first. Then move onto our query. But we were never able to successfully get it to work with the docs sample either.

Hello John,

I'm a newbie in the forum, very like working on MySQL. I just built Mysql 8.0.23 from source in MacOS and run your test case via mysql client command. It works for me.

Which mysql client do you use ? I'm not sure sql client that affect `EXPLAIN` query plan in this case.

mysql> EXPLAIN SELECT c1, c2 FROM t1 GROUP BY c1, c2;

| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | idx           | idx  | 66      | NULL |    5 |   100.00 | Using index for group-by |

Well, first we are not running it on MacOS and secondly the client is irrelevant to the query. Did you run the table create statement as I have pasted? Your explain plan is showing much different than ours; your type is range instead of index, and rows is 5 instead of 9.

mysql> EXPLAIN SELECT c1, c2 FROM t1 GROUP BY c1, c2;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | index | idx           | idx  | 99      | NULL |    9 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

ah It's my mistake as I try to reproduce the bug. Thank John!

This now shows as verified, is the status correct? Was someone able to verify this?

Indeed, the bug report has been verified.