Detailed Explanation of Mysql Efficient Paging

Preface

Typically, a "paging" strategy is adopted for queries with large data volumes on MySQL, but the queries will become slow if the pages are turned to a later position, because mysql will spend a lot of time scanning for data that needs to be discarded.

Basic paging skills

Typically, in order to achieve efficient paging, it is necessary to apply composite indexes to WHERE conditional columns and row sequences in queries.
For example, indexing (a, b, c) enables the following queries to use indexes and improve query efficiency:

1. Field sorting

ORDER BY a 
ORDER BY a,b
ORDER BY a, b, c 
ORDER BY a DESC, b DESC, c DESC 

2. Filtering and sorting

WHERE a = const ORDER BY b, c 
WHERE a = const AND b = const ORDER BY c 
WHERE a = const ORDER BY b, c 
WHERE a = const AND b > const ORDER BY b, c 

3. The following query cannot use the above index

ORDER BY a ASC, b DESC, c DESC// Sort direction is not 1 To 
WHERE g = const ORDER BY b, c //  Field g Not an index 1 Part 
WHERE a = const ORDER BY c // Fields are not used b 
WHERE a = const ORDER BY a, d //  Field d Not indexed 1 Part  

Solve the problem of turning pages with large amount of data

1. Change the query of LIMIT M and N to LIMIT N
For example, using LIMIT 10000, 20, Mysql will need to read the first 10000 rows and then get the next 20 rows, which is very inefficient. Using LIMIT N, conditional filtering is done by id of the first or last record on each page, and then the result set of the upper/lower page is obtained in descending and ascending order.
2. Limit the number of pages turned by users
In the actual use of products, users seldom care about the 10,000 data of search results.
3. Use delayed correlation
By using the override index to query and return the required primary key, and then associating the returned primary key with the original table to obtain the required rows, this can reduce the number of rows that need to be discarded by Mysql scanning.

Example:
Query using indexes (sex, rating):

mysql> SELECT <cols> FROM profiles INNER JOIN (
-> SELECT <primary key cols> FROM profiles
-> WHERE x.sex='M' ORDER BY rating LIMIT 100000, 10
-> ) AS x USING(<primary key cols>);