MySQL optimized order by using Filesort

The using filesort generally appears in the use of the order by statement.

The using filesort does not necessarily cause MySQL performance problems. However, if the number of queries is very many, then each time in MySQL to sort, or will have an impact.

The optimization method here is to index the fields in the order by, such as statements:

SELECT * from YW_SYJGB ORDER by result_date desc LIMIT 0,1000;

To view the execution plan:

+----+-------------+----------+------+---------------+------+---------+------+---------+----------------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+---------+----------------+
| 1 | Simple | YW_SYJGB | All | NULL | NULL | NULL | NULL | 1312418 | Using Filesort |
+----+-------------+----------+------+---------------+------+---------+------+---------+----------------+

You need to build an index on the result_date:

View the execution plan at this time:

+----+-------------+----------+-------+---------------+-------------+---------+------+------+-------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+----------+-------+---------------+-------------+---------+------+------+-------+
| 1 | Simple | YW_SYJGB | Index | NULL | Result_date | 6 | NULL | 1000 | NULL |
+----+-------------+----------+-------+---------------+-------------+---------+------+------+-------+

You can see that there is no using Filesort after the index is used in the execution plan

It is important to note that because the using filesort is used to sort in memory, MySQL has a limit on the size of the sorted record: Max_length_for_sort_data, default is 1024
Show variables like '%max_length_for_sort_data% ';

+--------------------------+-------+
| variable_name | Value |
+--------------------------+-------+
| Max_length_for_sort_data | 1024 |
+--------------------------+-------+
After the author tests, if the data of the sort query is more than this default value, you will still use the using Filesort.

In summary, when the amount of data for a sorted query is within the range of the default value, adding an index to the sorted field increases the speed of the MySQL query.

This article permanently updates the link address : http://www.linuxidc.com/Linux/2015-11/124881.htm

MySQL optimized order by using Filesort