MySQL's paging

A friend asked: MySQL paging seems to have been a problem, what is the optimization method? Online to see the online recommended some pagination method, but it seems not very feasible, can you review it?

　　Method 1: Directly use SQL statements provided by the database

---statement style: MySQL, you can use the following method: SELECT * from table name LIMIT m,n.

---adaptation scenario: suitable for small data volumes (tuple hundred/thousand).

---cause/disadvantage: Full table scan, very slow and some database result set return instability (such as a return to 2,1,3, another time return). Limit restricts the output of n from the m position of the result set and discards the rest.

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

---statement style: In MySQL, you can use the following methods:

SELECT * FROM table name WHERE id_pk > (pagenum*10) LIMIT M.

---Adaptation scenario: applies to multiple data volumes (tens of thousands of tuples).

---Cause: The index scan will be fast. Some friends suggest that because the data query is not sorted according to pk_id, so there will be the case of missing data, only method 3.

　　Method 3: Reorder based on indexes

---statement style: In MySQL, you can use the following method: SELECT * from table name WHERE id_pk > (pagenum*10) ORDER by ID_PK ASC LIMIT M.

---Adaptation scenario: applies to multiple data volumes (tens of thousands of tuples). It is best to have the Column object after the order by being the primary key or the unique, so that the on-line operation can take advantage of the index being eliminated but the result set is stable ( See Method 1).

---Cause: The index scan will be fast. But MySQL sort operation, only ASC no DESC (desc is fake, future will do real DESC, look forward).

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

---statement style: In MySQL, you can use the following methods:

PREPARE Stmt_name from SELECT * from table name WHERE id_pk > (? * ？ ) ORDER by ID_PK

ASC LIMIT M.

---adaptation scenario: Big Data volume.

---Cause: The index scan will be fast. The prepare statement is a bit faster than the normal query statement.

　　Method 5: Use the MySQL support order operation to quickly locate partial tuples using the index to avoid full table scanning

---such as: Read the 1000th to 1019th row tuple (PK is the primary key/unique key).

---SELECT * from your_table WHERE pk>=1000 ORDER by PK ASC LIMIT 0, 20.

　　Method 6: Use the subquery/Connect + Index to quickly locate the tuple's location, and then read the tuple. Principle and Method 5

---such as (ID is primary key/Unique key, blue font when variable):

Using a subquery example:

1234	select * from your_table where id <= ( select id from your_table order Code class= "SQL keyword" >by id desc limit ($page-1) * $pagesize order by id desc limit $pagesize

　Using the connection example:

12345

SELECT * FROM your_table AS t1JOIN (SELECT id FROM your_table ORDER BYid desc LIMIT ($page-1)*$pagesize AS t2WHEREt1.id <= t2.id ORDER BY t1.id desc LIMIT $pagesize;

　　Method 7: Stored procedure class (preferably fused above method 5/6)

---statement style: no longer given

---adaptation scenario: Big Data volume. the method recommended by the author

---Reason: it is relatively faster to encapsulate the operation on the server.

　　Method 8: Reverse method

---Online people write to use Sql_calc_found_rows. No reason, no imitation.

Basically, it can be generalized to all databases, and the truth is the same. However, method 5 may not be extended to other databases, the premise is that other databases support the order by operation can use the index to complete the sorting directly.

MySQL's paging