Bug #82171 Submitted: GROUP BY Optimization clarification 8 Jul 2016 19:24 27 Jul 2016 2:20 monty solomon Closed None MySQL Server: Documentation S3 (Non-critical) Any Any

[8 Jul 2016 19:24] monty solomon
```Description:
The section of the documentation about GROUP BY Optimization states:

The only aggregate functions used in the select list (if any) are MIN() and MAX(), and all of them refer to the same column. The column must be in the index and must follow the columns in the GROUP BY.

The phrase "must follow" is ambiguous and should be changed to "must immediately follow"

How to repeat:
Create table, add rows, and observe explain output to look for "Using index for group-by"

create table foo2(id serial, a int, b int, c int, d int, e int, index(a,b,c,d,e));
insert into foo2(a,b,c,d,e) values(1,2,3,4,5);
insert into foo2(a,b,c,d,e) values(1,2,3,4,5);
insert into foo2(a,b,c,d,e) values(1,2,3,4,5);
insert into foo2(a,b,c,d,e) values(1,2,3,4,5);
insert into foo2(a,b,c,d,e) values(1,2,3,4,5);
insert into foo2(a,b,c,d,e) values(1,2,3,4,5);
insert into foo2(a,b,c,d,e) values(1,2,3,4,5);
insert into foo2(a,b,c,d,e) values(1,2,3,4,5);
insert into foo2(a,b,c,d,e) values(1,2,3,4,5);
insert into foo2(a,b,c,d,e) values(1,2,3,4,5);
analyze table foo2;
explain select a, max(b) from foo2 group by a\G
explain select a, max(c) from foo2 group by a\G
explain select a, b, max(c) from foo2 group by a,b\G

mysql> create table foo2(id serial, a int, b int, c int, d int, e int, index(a,b,c,d,e));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into foo2(a,b,c,d,e) values(1,2,3,4,5);
Query OK, 1 row affected (0.00 sec)

mysql> insert into foo2(a,b,c,d,e) values(1,2,3,4,5);
Query OK, 1 row affected (0.00 sec)

mysql> insert into foo2(a,b,c,d,e) values(1,2,3,4,5);
Query OK, 1 row affected (0.01 sec)

mysql> insert into foo2(a,b,c,d,e) values(1,2,3,4,5);
Query OK, 1 row affected (0.00 sec)

mysql> insert into foo2(a,b,c,d,e) values(1,2,3,4,5);
Query OK, 1 row affected (0.00 sec)

mysql> insert into foo2(a,b,c,d,e) values(1,2,3,4,5);
Query OK, 1 row affected (0.00 sec)

mysql> insert into foo2(a,b,c,d,e) values(1,2,3,4,5);
Query OK, 1 row affected (0.00 sec)

mysql> insert into foo2(a,b,c,d,e) values(1,2,3,4,5);
Query OK, 1 row affected (0.00 sec)

mysql> insert into foo2(a,b,c,d,e) values(1,2,3,4,5);
Query OK, 1 row affected (0.00 sec)

mysql> insert into foo2(a,b,c,d,e) values(1,2,3,4,5);
Query OK, 1 row affected (0.00 sec)

mysql> analyze table foo2;
+-----------+---------+----------+----------+
| Table     | Op      | Msg_type | Msg_text |
+-----------+---------+----------+----------+
| test.foo2 | analyze | status   | OK       |
+-----------+---------+----------+----------+
1 row in set (0.01 sec)

mysql> explain select a, max(b) from foo2 group by a\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: foo2
type: range
possible_keys: a
key: a
key_len: 5
ref: NULL
rows: 3
Extra: Using index for group-by
1 row in set (0.00 sec)

mysql> explain select a, max(c) from foo2 group by a\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: foo2
type: index
possible_keys: a
key: a
key_len: 25
ref: NULL
rows: 10
Extra: Using index
1 row in set (0.00 sec)

mysql> explain select a, b, max(c) from foo2 group by a,b\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: foo2
type: range
possible_keys: a
key: a
key_len: 10
ref: NULL
rows: 3
Extra: Using index for group-by
1 row in set (0.00 sec)

Suggested fix:
Update the documentation

http://dev.mysql.com/doc/refman/5.5/en/group-by-optimization.html

http://dev.mysql.com/doc/refman/5.6/en/group-by-optimization.html

http://dev.mysql.com/doc/refman/5.7/en/group-by-optimization.html```
[8 Jul 2016 19:32] monty solomon
`Or update the loose index scan to use any following column in the index.`
[12 Jul 2016 14:04] MySQL Verification Team
```You are quite right.

Verified.```
[27 Jul 2016 2:20] Paul DuBois
`Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly.`