Summary of MySQL order

Reference material Jian Chaoyang blog

 

I recently learned how to optimize MySQL order. Summary:

 

If there is no WHERE clause: the query field must be consistent with the order by field to use the index. Example: select key from tbl1 order by key;

 

If there is a where clause: two steps are performed. The first step is to query the appropriate data using the where condition; the second step is to sort the results of the first step.

 

1) if the index used by the WHERE clause is exactly the same as that used in order by, MySQL will omit step 2. Because the first step of msyql has sorted the order according to the fields in order.

2) If the index used by the WHERE clause is different from that used in order by, MySQL will first retrieve the data that meets the WHERE clause and then sort the data.

3) in the 2nd) operation, if the sorting data is joined through multiple tables, MySQL will first create a temporary table and then sort the data on the temporary table.