Advanced pagination for MySQL (MySQL advanced paging) _mysql

In the leaf article, we talked about using the inner join to reduce the scanning of the page, which is reducing the so-called back table for example:

Copy Code code as follows:

SELECT * from ' T1 ' INNER JOIN (select id from ' T1 ' ORDER by ID DESC LIMIT 935500,10) T2 USING (ID)

By directly manipulating the ID rather than the whole table scan through the join of the ID to grab the qualifying ID and then the data crawl through the ID. This avoids the need for a scan of the page.

But that's not the best way to do it. You can also narrow down the range of IDs by using the range for example:

We're going to divide it into 100 records, one page to write.

Copy Code code as follows:

$page _size=100 select * from t where ID > The order by ID ASC limit $page _size; SELECT * FROM t where ID >199 the ORDER by ID ASC limit $page _size;

Try to avoid limit m,n this writing MySQL in the M value is very large and offset is very small when the processing is very impersonal, so try not to use offset to get a specific number of rows.

There is a problem here, for example, depending on a column page that is not a unique index, there may be a problem, for example, if there are 11 key=100 values for a row column1, then the Min value that you use limit N is the same value.
How do you deal with this situation? Give an example:

Like a page for every 10 records.

Copy Code code as follows:

SELECT * FROM T-order by column1 DESC LIMIT 10

Note that the Min value or 100 (11 consecutive 100) will have an impact on the following pagination, how to handle it?

Yahoo gives a very good solution to take a extra column such as PK or unique index key for example:

Copy Code code as follows:

SELECT * FROM T-column1 desc, id desc limit 10--first page
SELECT * FROM t where Column1 <=minvalue_col1 and (ID < minvalue_id or column1 < MINVALUE_COL1) Limit---Second Page

This ensures that the uniqueness ensures that the data on each page does not duplicate the idea is to extra the extra boundary value with the range column.

This SQL can also be optimized to:

Copy Code code as follows:

SELECT m2.* from T M1, t m2 WHERE m1.id = m2.id and M1.column1 <=
and (M1.id < minvalue_id OR M1.column1 < minvalue_col1) Order by M1.column1 DESC, m1.id DESC LIMIT 10;

Core idea: Through extra filter with ID scan to avoid a large number of back-table operations so as to achieve how many to take to scan the number of (in page)