Description of using filesort in Mysql EXPLAIN

Source: Internet
Author: User

Syntax format:
EXPLAIN tbl_name
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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.