A foreground SQL statement is simplified as follows:
SELECT products_name, products_viewed FROM 'products _ description'
Order by products_viewed DESC, products_name LIMIT 0, 20;
This statement often appears in large batches in slow logs!
A preliminary look at the change statement is very simple. sort by products_viewed (number of product views) in reverse order, and then sort by products_name (Product Name! Create an index on products_viewed and products_name respectively!
However, the ordering of products_name is strange!
After explaining, we found
+ ---- + ------------- + ---------------------- + ------ + --------------- + ------ + --------- + ------ + ------- + ------------------ +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ---------------------- + ------ + --------------- + ------ + --------- + ------ + ------- + ------------------ +
| 1 | SIMPLE | products_description | ALL | NULL | 764370 | Using filesort |
+ ---- + ------------- + ---------------------- + ------ + --------------- + ------ + --------- + ------ + ------- + ------------------ +
Change the statement to scan the entire table!
Mysql's order by statement, if there is no suitable index selection in the where condition, the index in order by col will be selected as the condition, but if it is a combination of multiple order by statements, the index will be discarded!
Communicate with developers and requirements, and find that sorting by name is not needed!
Remove products_name after order!
After explaining again, we found that indexes can be used:
Explain SELECT products_name, products_viewed FROM 'products _ description'
Order by products_viewed LIMIT 0, 20;
+ ---- + ------------- + ---------------------- + ------- + --------------- + ----------------- + --------- + ------ + ------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ---------------------- + ------- + --------------- + ----------------- + --------- + ------ + ------- +
| 1 | SIMPLE | products_description | index | NULL | products_viewed | 5 | NULL | 20 |
+ ---- + ------------- + ---------------------- + ------- + --------------- + ----------------- + --------- + ------ + ------- +
Compare two times of profiling again (process omitted), and find that a large number of io and cpu time Sorting results are damaged for the first time! This statement is a front-end statement with a large number of queries. After optimization, the page opening speed is significantly improved!
Note:
1. order by m, n do not write this statement easily. Generally, m in front of order by is the focus of order by, and n in the latter is the secondary role. If not necessary, try to remove it.
2. Refer to my other article