11

I've noticed again and again that when I EXPLAIN queries with a GROUP BY clause, I get filesort as an extra condition. A long time ago I read a suggestion to use GROUP BY NULL in these cases to avoid the filesort, and it does indeed eliminate that unsavoury looking filesort condition.

I would think that if no ORDER BY clause is present that the dbms would just present an arbitrary order or whatever is most efficient rather than ordering by some mysterious column which requires a filesort. It seems strange to me that I need to include an extra direction which basically amounts to saying "don't do anything stupid".

My question is why is this even necessary and is adding ORDER BY NULL actually helping performance?

Improve this question

2 Answers 2

Reset to default
13

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

Improve this answer
6
  • Thanks, never caught that bit in the docs. That makes sense but it's interesting that the docs also state "Relying on implicit GROUP BY sorting (that is, sorting in the absence of ASC or DESC designators) is deprecated" but apparently it's still happening despite being deprecated. Would be nice if they completely removed this behaviour. Makes no sense to have to override something which isn't even guaranteed to be happening.
    – Eaten by a Grue
    May 29, 2018 at 19:01
  • @billynoah All claims and offers send to the Oracle company...
    – Akina
    May 29, 2018 at 19:18
  • 2
    @billynoah - MySQL's feature of GROUP BY implying an ordering is non-standard. Oracle is probably afraid to break customer's SQL that depends on it (either deliberately or accidentally). Rule: If you want an order, say ORDER BY; let the Optimizer throw it out when possible.
    – Rick James
    May 30, 2018 at 3:23
  • @RickJames - I agree and that's what I always assumed. Until today I didn't realize the MySQL was doing behind the scenes "implicit" ordering on grouped data and thereby requiring me to explicitly tell it to NOT ORDER the data to prevent a performance hit.
    – Eaten by a Grue
    May 30, 2018 at 3:28
  • 2
    @billynoah - I think the history is something like this... In the dark ages, the only implementation of GROUP BY was to sort the data, then walk though it gathering one row per 'group'. Later, the code got smarter, such as with a hash table without needing to sort. But users were depending on the implicit sort. Stuck. Note also, there is a little-known syntax: GROUP BY x ASC
    – Rick James
    May 30, 2018 at 3:43
4

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)
Improve this answer
7
  • although I appreciate your answer, I don't think this is always the case. The case that happened today which prompted my question, the group by column was the table's primary key so an index did indeed exist but it was using filesort regardless. there were joins involved but all tables were being joined by columns with indexes. we're a bit off topic now but can you explain why I'd be getting filesort on a query where everything involved has the appropriate indexes?
    – Eaten by a Grue
    May 30, 2018 at 1:26
  • @billynoah What is the meaning of grouping by PK that is UNIQUE by definition? I suspect that column used for grouping is the part of multicolumn PK but hasn't separate index suitable for grouping/sorting by that column solely.
    – Kondybas
    May 30, 2018 at 2:06
  • Something like this: SELECT * FROM animal JOIN animal_color USING (animal_id) GROUP BY animal_id. In this example animal_id is primary key of first table which has a few colors per animal and an index on animal_id. I still get filesort in this example.
    – Eaten by a Grue
    May 30, 2018 at 2:40
  • that produces the exact same plan
    – Eaten by a Grue
    May 30, 2018 at 2:58
  • I'm not sure what you're actually trying to answer but here you go: sqlfiddle.com/#!9/6fa9c1/2 run explain on that query (on a real database, not in sqlfiddle) you will see filesort in the extras
    – Eaten by a Grue
    May 30, 2018 at 3:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.