MySQL paging technology, 6 kinds of pagination method summary

This article mainly introduced the MySQL paging technology, 6 kinds of pagination method summary, this article summarized 6 kinds of pagination method and respectively one by one explained their characteristic, needs the friend may refer to under

Overview

A friend asked: MySQL pagination seems to have been a problem, what is the optimization method?

Online See net XX recommended a number of pagination method, but it seems not very feasible, can you comment on it?

Method Summary

Method 1: Directly use the SQL statements provided by the database

Statement style: In MySQL, you can use the following methods: SELECT * FROM table name LIMIT m,n

Adaptation scenario: Suitable for cases with less data (META Group/Chi Pe)

Reason/disadvantage: Full table scan, slow and some database result set return instability (such as a return to 1,2,3, another return 2,1,3). The limit limit is to remove the n output from the m position of the result set and discard the remainder.

Method 2: Create a primary key or unique index, using the index (assuming 10 per page)

Statement style: In MySQL, you can use the following methods: SELECT from table name WHERE id_pk > (PAGENUM10) LIMIT M

Adaptation scenario: Applicable to a lot of data (thousands of tuples)

Reason: Index scan, speed will be very fast. A friend suggested: Because the data query is not sorted according to pk_id, so there will be missing data, only method 3

Method 3: Reorder based on indexes

Statement style: In MySQL, you can use the following methods: SELECT from table name WHERE id_pk > (PAGENUM10) Order by ID_PK ASC LIMIT M

Adaptation Scenario: Applies to a lot of data (the number of thousands of tuples). A Column object with the best order by is a primary key or unique so that the by operation can take advantage of the index to be eliminated but the result set is stable (see Method 1 for stable meaning)

Reason: Index scan, speed will be very fast. But the MySQL sort operation, only ASC no DESC (desc is fake, future will do real DESC, look forward to ...).

Method 4: Use prepare based on index (the first question mark denotes pagenum, the second? Represents the number of tuples per page)

Statement style: MySQL, the following methods can be used: PREPARE stmt_name from SELECT from table name WHERE id_pk > (??) ORDER by ID_PK ASC LIMIT M

Adaptive scene: Large amount of data

Reason: Index scan, speed will be very fast. A prepare statement is a bit faster than a normal query statement.

Method 5: Stored procedure class (preferably fused above method 4)

Statement style: No longer give the

Adaptation scene: Large amount of data. Methods recommended by the authors

Reason: The operation is encapsulated in the server, relatively faster.