Re: Query with GROUP BY (Using index for group-by -> Using index)

[mnf]

Much worse:

mysql> explain SELECT MAX(i) AS maxi FROM shn WHERE i <= 1000000000 GROUP BY n;
+----+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+
| 1 | SIMPLE | shn | range | PRIMARY | ni | 6 | NULL | 217 | Using where; Using index for group-by |
+----+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+
1 row in set (0.00 sec)

mysql> explain SELECT group_concat(maxi) FROM (SELECT MAX(i) AS maxi FROM shn WHERE i <= 10000000 GROUP BY n) as T1;
+----+-------------+------------+-------+---------------+------+---------+------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+------+---------+------+---------+--------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 216 | |
| 2 | DERIVED | shn | index | PRIMARY | ni | 6 | NULL | 7699628 | Using where; Using index |
+----+-------------+------------+-------+---------------+------+---------+------+---------+--------------------------+
2 rows in set (8.18 sec)

mysql> SELECT MAX(i) AS maxi FROM shn WHERE i <= 1000000000 GROUP BY n;
...
216 rows in set (0.00 sec)

mysql> SELECT group_concat(maxi) FROM (SELECT MAX(i) AS maxi FROM shn WHERE i <= 10000000 GROUP BY n) as T1;
...
1 row in set, 1 warning (8.05 sec)