GROUP BY needs ORDER BY NULL to avoid filesort
MySQL 5.7 Reference Manual / ... / SELECT Syntax
If you use GROUP BY, output rows are sorted according to the GROUP BY columns as if you had an ORDER BY for the same columns. To avoid the overhead of sorting that GROUP BY produces, add ORDER BY NULL
Using filesort
appears when column(s) used for grouping does not have an appropriate index. As mentioned above, results returned by GROUP BY
are ordered by the same column(s). If you get filesort
for sorting you also have filesort for grouping. That insult performance in the same way. Therefore you have to create the index, not to suppress the sorting.
EXPLAIN
SELECT w.t_id
, count(1) AS counter
FROM points AS w
GROUP BY w.t_id
;
+----+-------------+-------+-------+---------------+-------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------+---------+------+----------+-------------+
| 1 | SIMPLE | w | index | t_id | t_id | 2 | NULL | 27228500 | Using index |
+----+-------------+-------+-------+---------------+-------+---------+------+----------+-------------+
No filesort
without ORDER BY NULL
.
SELECT w.t_id, count(1) AS counter FROM points AS w GROUP BY w.t_id;
/* Affected rows: 0 Found rows: 606 Warnings: 0 Duration for 1 query: 6,922 sec. */
SELECT w.t_id, count(1) AS counter FROM points AS w GROUP BY w.t_id ORDER BY NULL;
/* Affected rows: 0 Found rows: 606 Warnings: 0 Duration for 1 query: 6,781 sec. */
P.S.
As far as fidlle fails here is the mysql
output:
No multicolumn index:
+----+-------------+---------------+------+-----------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+------+-----------------+------+---------+------+------+----------------------------------------------------+
| 1 | SIMPLE | animals | ALL | PRIMARY | NULL | NULL | NULL | 3 | Using temporary; Using filesort |
| 1 | SIMPLE | animal_colors | ALL | animal_id,color | NULL | NULL | NULL | 6 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+---------------+------+-----------------+------+---------+------+------+----------------------------------------------------+
2 rows in set (0.00 sec)
Multicolumn index added:
+----+-------------+---------------+-------+---------------------------------+-----------+---------+------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+-------+---------------------------------+-----------+---------+------------------------+------+-------------+
| 1 | SIMPLE | animals | index | PRIMARY | PRIMARY | 4 | NULL | 3 | NULL |
| 1 | SIMPLE | animal_colors | ref | animal_id,color,animal_id_color | animal_id | 4 | test.animals.animal_id | 1 | Using where |
+----+-------------+---------------+-------+---------------------------------+-----------+---------+------------------------+------+-------------+
2 rows in set (0.00 sec)