MySQL Tuning-Using Filesort

This problem occurs because MySQL can use only one index per query, and your SQL statement WHERE condition and order by conditions are not the same, the index is not built, then the ORDER by is not used to index, there is a using filesort problem.
The solution to this problem is to create a hybrid index that contains the WHERE and ORDER by conditions.

For example, the original SQL statement is:

SELECT * from the user U where u.id=100 order by u.update_time

And the index is idx_user_id (ID)
Now re-establish the index to Idx_user_id_update_time (id,update_time) and use the EXPLAIN command to see if key is using the new Idx_user_id_update_time index above, you can see The using file sort problem disappears, and if key is not using the new Idx_user_id_update_time index, you can use the Force index () method to enforce the index, at which point the using Filesort problem is resolved.

SELECT * from User U Force index (idx_user_id_update_time) where u.id=100 order by u.update_time

MySQL Tuning-Using Filesort