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