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)