Detailed explanation of efficient MySQL paging

Preface

Usually, a "paging" strategy is adopted for MySQL queries with large amounts of data. However, if the page is turned to a later position, the query will become very slow because MySQL will spend a lot of time scanning the data that needs to be discarded.

Basic paging techniques

Usually, in order to achieve efficient paging, you need to apply a combined index to the WHERE condition column and the sort column in the query.
For example, creating an index (a, b, c) allows the following query to use the index, improving 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. Filter and sort

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 // The sorting direction is inconsistent WHERE g = const ORDER BY b, c // Field g is not part of the index WHERE a = const ORDER BY c // Field b is not used 
WHERE a = const ORDER BY a, d // Field d is not part of the index

Solve the problem of page turning for large amounts of data

1. Change the query of LIMIT M,N to LIMIT N
For example, with LIMIT 10000,20, MySQL will need to read the first 10,000 rows and then get the next 20 rows, which is very inefficient. With LIMIT N, you can filter by the ID of the first or last record on each page, and then use descending and ascending to get the result set of the previous/next page.
2. Limit the number of pages users can turn. In actual product usage, users rarely care about the 10,000th search result.
3. Use delayed association
By using a covering index to query and return the required primary key, and then associating the original table with the returned primary key to obtain the required rows, the number of rows that Mysql scans and needs to discard can be reduced.

Examples:
Query using the index (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>);

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.