Summary of 8 Mysql paging methods _mysql

MySQL pagination seems to have been a problem, what is the optimization method? Online to see some of the online recommended pagination method, but it does not seem feasible, can you comment on it?

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 a small amount of data (tuple hundred/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:

Copy Code code as follows:

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

---Adaptation scenario: Applies to a lot of data (thousands of tuples).

---Reason: Index scan, speed will be very fast. Some friends put forward 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 > (pagenum*10) Order by ID_PK ASC LIMIT M.

---Adaptation scenario: Applies to a lot of data (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, the future will do real DESC, look forward).

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

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

Copy Code code as follows:

PREPARE Stmt_name from SELECT * from table name WHERE id_pk > (? * ？ ) Order BY ID_PK
ASC LIMIT M.

---adaptation 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: Using the MySQL support order operation can use the index to quickly locate part of the tuple to avoid full table scan

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

Copy Code code as follows:

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

Method 6: Use the subquery/join + Index to quickly locate the tuple, and then read the tuple. The same method 5

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

Using the subquery example:

Copy Code code as follows:

select* fromyour_table Whereid <=
(Selectid fromyour_table Order
Byid Desclimit ($page-1) * $pagesize Orderbyid desc
LIMIT $pagesize

Use the connection example:

Copy Code code as follows:

select* fromyour_table ASt1
JOIN (Selectid fromyour_table by
ID Desclimit ($page-1) * $pagesize ASt2
WHERE
T1.id <= t2.id orderbyt1.id desclimit $pagesize;

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

---statement style: no longer given

---adaptation scene: large amount of data. Methods recommended by the authors

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

Method 8: Negative methods

---people on the Internet write using Sql_calc_found_rows. There is no reason to imitate.

Basically, you can generalize to all the databases, and the truth is the same. However, method 5 may not be extended to other databases, and the prerequisite is that other databases support an order by operation that can be sorted directly using the index.