Description of using filesort in Mysql EXPLAIN

Syntax format:
EXPLAIN tbl_name
Or:
Explain select select_options
The EXPLAIN statement can be used as a synonym for DESCRIBE, or used to obtain information about the SELECT statement to be executed by MySQL.

The EXPLAIN tbl_name syntax is the same as DESCRIBE tbl_name or show columns from tbl_name.

When the keyword "EXPLAIN" is used before a SELECT statement, MYSQL explains how to run the SELECT statement, and displays the table connection and connection sequence.

The following information is a reference:

When explaining the SQL statements we use, we often encounter using filesort. I thought it was caused by the same column value. As a result, I saw an SQL statement from the company yesterday, after discussing with colleagues, I did some tests and suddenly found that my original ideas were wrong.

First, using filesort may only appear when order by data columns. If you do not set an index for this column of order, using filesort will appear no matter whether the column values are the same. Therefore, as long as the order by column is used, an index should be created for it.

Secondly, in this test, a slightly complex example is used to illustrate this problem. The following is a detailed example:

SELECT * from db. tb where id = 2222 and fid in (,) order by INVERSE_DATE LIMIT 0, 5
The index is a three-column multi-column index: IDX (ID, FID, INVERSE_DATE ). INVERSE_DATE is the reverse index of time.

For this SQL statement, I initially thought it should be a well-optimized state, and there should be no missing details. The result was explained to find that: Using where; Using filesort exists.

Why? Later, after analysis, I learned that the original multi-column index was created in the B-tree structure. Therefore, the index was first created in the order of IDs, create an index with the same ID in the order of FID, and create an index in the order of INVERSE_DATE in the case of the same FID. If there are more columns, and so on. With this theoretical basis, we can see that this IDX index is used only before order by, and order by INVERSE_DATE is actually using filesort .. Khan is dead .. Therefore, if we want to optimize this SQL statement, we should create another index IDX (ID, INVERSE_DATE) for it, so that the using filesort speed will be much faster. The problem is finally solved.