Understand filesort in the Explain command output

Some time ago I read the last article about filesort in mysqlperformanceblog, And I saw Percona often asked the applicant "what is the meaning of filesort IN THE Explain command output" during the recruitment interview, it is strange that the correct answer is basically not obtained. This is a very basic content.

Some time ago I read the last article about filesort on mysqlperformance blog. When I saw Percona's job interview, I often asked the applicant the question "what is the meaning of filesort IN THE Explain command output, it is strange that the correct answer is basically not obtained. This is a very basic content.

Some time ago I read the last article about filesort on mysqlperformance blog. When I saw Percona's job interview, I often asked the applicant the question "what is the meaning of filesort IN THE Explain command output, it is strange that the correct answer is basically not obtained.

This is a very basic content. Since you dare to apply for a job from a company like Percona, you should not be clear about this problem.

So I also tentatively asked a few friends in the MySQL circle this question. The result is basically the same as that of Percona. only 1/10 of my friends really understand the meaning of filesort, the answer to the error is similar to the answer from Percona. It is because the temporary table is too large during mysql sorting and exceeds the MySQL limit. You need to write the data to the disk file for sorting, this is called filesort.

Most of the "seemingly correct" answers are probably guessed by the literal meaning of the word "filesort. It seems that not only Chinese people like "Wang wenshengyi", but also foreigners!

What does filesort in the output of the Explain command mean? In fact, it is very easy to tell you That MySQL needs to perform actual sorting operations instead of obtaining sorted data through indexes.

I personally think the above incorrect answer is at least two points:

• Filesort (in fact, sorting) may not necessarily generate temporary tables.
• There is no direct connection between filesort and the writing of temporary table data to the disk.

The first point in the above two errors is also mentioned in the MySQL Performance Blog article.

In fact, the implementation analysis of MySQL order by in my previous article has clearly analyzed MySQL's sorting, the temporary table must be used only when the returned data and sorting conditions are not in the same table.

It is really very rigorous to learn the technology. If we simply guess its meaning by literal meaning, without carefully reading the document and analyzing the experiment, the answer may be wrong in many cases.

Original article address: Understand filesort in the Explain command output. Thank you for sharing it with the original author.