What are the basic principles of MySQL pagination

This article mainly introduces "what are the basic principles of MySQL pagination". In daily operation, I believe many people have doubts about the basic principles of MySQL pagination. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts of "what are the basic principles of MySQL pagination?" Next, please follow the editor to study!

Mysql > explainSELECT*FROMmessageORDERBYidDESCLIMIT10000,20\ G*1.row*id:1select_type:SIMPLEtable:messagetype:indexpossible_keys:NULLkey:PRIMARYkey_len:4ref:NULLrows:10020Extra:1rowinset (0.00sec)

Limit10000,20 means to scan 10020 rows that meet the criteria, throw away the previous 10000 rows, and return the last 20 rows. This is the problem. If it is limit100000,100, 100100 rows need to be scanned. In a highly concurrent application, the performance of each query needs to be scanned more than 10W, which must be greatly compromised. It is also mentioned that limitn performance is fine because only n lines are scanned.

How to build efficient MySQL pagination

The article mentions a "clue" practice, which provides some "clues" to page flipping, such as SELECT*FROMmessageORDERBYidDESC, 20 pages per page according to id descending order, currently page 10, the maximum id of the current page entry is 9527 and the smallest is 9500. If we only provide jumps such as "previous page" and "next page" (no jump to page N), then when dealing with "previous page", the SQL statement can be:

SELECT*FROMmessageWHEREid > 9527ORDERBYidASCLIMIT20

When dealing with "next page", the SQL statement can be:

SELECT*FROMmessageWHEREid9527ORDERBYidASCLIMIT20,20

Jump to page 13:

SELECT*FROMmessageWHEREid