This problem occurs because MySQL can only use one index for each query. The WHERE condition of your SQL statement is different from that of ORDERBY. if the index is not created, ORDERBY cannot use the index, the reason for this problem occurs when Usingfilesort occurs is that MySQL can only use one index for each query, and the WHERE condition of your SQL statement is different from the ORDER BY condition. if the index is not created properly, order by cannot use the index, and the Using filesort problem occurs.
To solve this problem, create a hybrid index containing the WHERE and order by conditions.
For example, the original SQL statement is:
SELECT * FROM user u where u. id = 100 order by u. update_time
The index is idx_user_id (id)
Now re-create the index as idx_user_id_update_time (id, update_time) and run the EXPLAIN command to view the index. if the key uses the newly created idx_user_id_update_time index, the Using file sort problem disappears, if the key does not use the new idx_user_id_update_time index, you can use the force index () method to forcibly use this index. in this case, the using filesort problem is solved.
SELECT * FROM user u force index (idx_user_id_update_time) where u. id = 100 order by u. update_time
