Using filesort caused by order by optimization by MySQL

Using filesort caused by order by optimization by MySQL

Using filesort is generally used in the order by statement.

Using filesort does not necessarily cause mysql performance problems. However, if the number of queries is very large, sorting in mysql will still be affected.

The optimization method here is to create an index for the order by field, such as the statement:

SELECT * FROM yw_syjgb order by result_date desc LIMIT 0,1000;

View the execution plan:

+ ---- + ------------- + ---------- + ------ + --------------- + ------ + --------- + ---------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ---------- + ------ + --------------- + ------ + --------- + ---------------- +
| 1 | SIMPLE | yw_syjgb | ALL | NULL | 1312418 | Using filesort |
+ ---- + ------------- + ---------- + ------ + --------------- + ------ + --------- + ---------------- +

You need to create an index in result_date:

View the execution plan:

+ ---- + ------------- + ---------- + ------- + --------------- + ------------- + --------- + ------ + ------- +
| 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 |
+ ---- + ------------- + ---------- + ------- + --------------- + ------------- + --------- + ------ + ------- +

We can see that there is no Using filesort after indexes are used in the execution plan.

Note: Because Using filesort uses algorithms to sort records in memory, MySQL also limits the size of sorting records: max_length_for_sort_data. The default value is 1024.
Show variables like '% max_length_for_sort_data % ';

+ -------------------------- + ------- +
| Variable_name | Value |
+ -------------------------- + ------- +
| Max_length_for_sort_data | 1024 |
+ -------------------------- + ------- +
After the author's test, if the data in the sorting query is greater than the default value, Using filesort will still be used.

To sum up, when the data volume of the sorting query is within the default range, adding an index to the sorting field can speed up the MySQL query.

This article permanently updates the link address: