Example of optimizing mysql limitoffset _ MySQL

Example of optimizing mysql limitoffset: bitsCN.com

One common problem is that the offset of limit is too high, such as: limit 100000,20. in this way, the system will query 100020 entries and then discard all the preceding 100000 entries. this is a very costly operation, the query is slow. Suppose that the page access frequency of all pages is the same, so that the average query scans half of the table data. The optimization method either limits the number of pages after access or improves the query efficiency of high offset.

A simple optimization method is to use covering index to query and then perform the join operation with the entire row. For example:


SQL> select * from user_order_info limit 0, 5;

This statement can be optimized:

Select * from user_order_info inner join (select pin from user_order_info limit 00,5) as lim using (pin );
SQL> explain select * from user_order_info limit;
+ ---- + ------------- + ----------------- + ------ + --------------- + ------ + --------- + ------ + ---------- + ------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ----------------- + ------ + --------------- + ------ + --------- + ------ + ---------- + ------- +
| 1 | SIMPLE | user_order_info | ALL | NULL | 23131886 |
+ ---- + ------------- + ----------------- + ------ + --------------- + ------ + --------- + ------ + ---------- + ------- +
1 row in set (0.00 sec)
SQL> explain extended select * from user_order_info inner join (select pin from user_order_info limit 00,5) as lim using (pin );
+ ---- + ------------- + ----------------- + -------- + --------------- + --------- + ------------ + ---------- + ------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+ ---- + ------------- + ----------------- + -------- + --------------- + --------- + ------------ + ---------- + ------------- +
| 1 | PRIMARY | | ALL | NULL | 5 | 100.00 |
| 1 | PRIMARY | user_order_info | eq_ref | PRIMARY | 42 | lim. pin | 1 | 100.00 |
| 2 | DERIVED | user_order_info | index | NULL | PRIMARY | 42 | NULL | 23131886 | 100.00 | Using index |
+ ---- + ------------- + ----------------- + -------- + --------------- + --------- + ------------ + ---------- + ------------- +
3 rows in set, 1 warning (0.66 sec)


Based on the comparison of the two explain statements, we can clearly find that the first index is not used, and 23131886 rows are scanned. The second one also scans the same number of rows, but the index is used, improving the efficiency. In this way, you can directly use index to obtain data, instead of querying the table. after finding the required data, join the entire table to obtain other columns.

BitsCN.com