Bug #30039 Add "Using group-by" to Extra column in EXPLAIN output
Submitted: 25 Jul 2007 13:35 Modified: 25 Jul 2007 21:38
Reporter: Baron Schwartz (Basic Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S4 (Feature request)
Version: OS:Any
Assigned to: CPU Architecture:Any
Tags: qc

[25 Jul 2007 13:35] Baron Schwartz
Description:
I read that there's an effort to add more information to EXPLAIN.  One thing I don't see in EXPLAIN is where a GROUP BY is being performed.  Though you can examine the query and see, you can't examine EXPLAIN alone and see.  Even when you have the query text, it's often not obvious at which stage MySQL really performs the GROUP BY.

There is one special case, "Using index for group-by" but it only applies in limited circumstances, and is really an indication of how an index is being used (loose index scan).  Its primary purpose isn't to show that there's a GROUP BY.  It can even show up in a non-grouped DISTINCT query

How to repeat:
Here are some examples:

mysql> explain select Host from mysql.user group by Host;
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | user  | range | NULL          | PRIMARY | 180     | NULL |    1 | Using index for group-by | 
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.01 sec)

mysql> explain select count(*) from mysql.user group by Host;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | index | NULL          | PRIMARY | 228     | NULL |   10 | Using index | 
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select distinct Host from mysql.user;
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | user  | range | NULL          | PRIMARY | 180     | NULL |    1 | Using index for group-by | 
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+

Suggested fix:
I propose adding another value to the Extra column: "Using group-by."  I chose this wording because it seems consistent with "Using where."  In cases where there's a grouped JOIN and thus multiple rows in EXPLAIN, I am not sure which row it should appear in.  Probably the optimizer developers can make a better suggestion.
[25 Jul 2007 21:38] Valeriy Kravchuk
Thank you for a reasonable feature request.