MySQL-which is more efficient with array_slice paging in PHP arrays and querying with limit?

Used to use the limit to page, now found Array_slice page is very useful, is not aware of the efficiency will not be too low

Reply content:

Used to use the limit to page, now found Array_slice page is very useful, is not aware of the efficiency will not be too low

Sql:select * FROM table limit m offset N.

This is the business requirement for normal paging.

Execute SQL on a table with Count 200w:
SELECT * FROM table limit 1000000,5

Execution time: 25s

Problem:
The way to handle limit offset in MySQL is to remove all the data from the Offset+limit, then remove offset and return to the bottom limit.
This way in the case of high offset, such as: Limit 100000, 20, so that the system will query 100,020, and then the preceding 100,000 are thrown away, this is a very expensive operation, resulting in slow query slow.

How to optimize:

1. Using ID>M limit n instead of m,n is much faster than using limit, because the primary key index is used and only n records are queried. This approach is well suited for data loading, but not necessarily suitable for front-page paging scenarios,
   Because the ID may not be contiguous, it is not applicable in the page of the elevator mode, just the page with escalator mode.
   SELECT * FROM table where ID > 1000000 limit 5
   Execute: 0.013s

There is also a simple optimization method that uses the overwrite query (covering index) query, and then the join operation with the full line. This allows you to get the data directly using index instead of querying the table, and when you find the data you want, in the join with the full table, get the other columns.
Such as:
SELECT * FROM table INNER JOIN (SELECT ID from table limit 1000000,5) as Lim on table. id = lim.id

Execution Time: 0.211s

Array_slice page? You're sure you want to find out all about it? Paging through the server, rather than querying the database for data on a page

