Search code examples
mysqlsqlselectsql-order-byexplain

what does Using filesort mean in MySQL?

I want to know what does Using filesort mean in MySQL?

explain select * from user order by user_id;

It turn up when i use 'order by'.

1,SIMPLE,orderitems,,ALL,,,,,1,100,Using filesort

The 'Using filesort' is in extra. Thanks for everyone!

Solution

  • This is actually in the MySQL documentation:

    https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain-extra-information

    Using filesort (JSON property: using_filesort)

    MySQL must do an extra pass to find out how to retrieve the rows in sorted order. The sort is done by going through all rows according to the join type and storing the sort key and pointer to the row for all rows that match the WHERE clause. The keys then are sorted and the rows are retrieved in sorted order. See Section 8.2.1.16, “ORDER BY Optimization”.

    When you don't see "using filesort," it means the order you requested for the query result matches the natural order in which the data was read, so there was no extra work needed to sort the result.

    When querying InnoDB tables (the default storage engine), MySQL queries always read the rows by the order of the index reported by EXPLAIN. If the order it read rows matches the order the query requested, then the query simply returns rows as it reads them. If the order the rows are read is different from the order you requested, then the query must sort the results before returning them.