MySQL Tuning--using Filesort

Tag: The user using ROM uses the RAC statement from the ORDER by nbsp

The solution to this problem is that MySQL can use only one index per query, and your SQL statement's WHERE condition is not the same as the condition of ORDER by, and the index is not built. Then the ORDER by is not used for indexing. A Using filesort problem has occurred.
The problem is to create a hybrid index that includes 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)
It is now again indexed to Idx_user_id_update_time (id,update_time) and then viewed using the EXPLAIN command. Assume that key is using the newly created Idx_user_id_update_time index above. You can see that the Using file sort problem disappears. Assume that key is not using the new Idx_user_id_update_time index. The ability to use the Force Index () method forces the index to be used, at which point the using Filesort problem is overcome.

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

MySQL Tuning--using Filesort