Mysql paging technology, 6 kinds of pagination method summary _mysql

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, 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.

Method 6: Negative methods

People on the Internet write using Sql_calc_found_rows. No reason to imitate