Summary of MySQL paging technology

A friend asked: MySQL paging seems to have always been a problem. Is there any optimization method? I saw some paging methods recommended online, but they don’t seem feasible. Can you comment on them?

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

---Statement style: In MySQL, the following method can be used: SELECT * FROM table name LIMIT M,N.

---Applicable scenarios: Suitable for situations where the amount of data is small (tuple levels of hundreds/thousands).

---Reason/disadvantage: Full table scan will be very slow and some database result sets return unstable (such as returning 1,2,3 one time and 2,1,3 another time). Limit restricts N outputs from M positions in the result set and discards the rest.

Method 2: Create a primary key or unique index and use the index (assuming 10 entries per page)

---Statement style: In MySQL, the following methods are available:

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

---Applicable scenarios: Suitable for situations with large amounts of data (tens of thousands of tuples).

---Reason: Index scanning will be very fast. A friend suggested that because the data query is not sorted according to pk_id, there will be cases of missing data, and the only option is method 3.

Method 3: Reorder based on index

---Statement style: In MySQL, the following method can be used: SELECT * FROM table name WHERE id_pk > (pageNum*10) ORDER BY id_pk ASC LIMIT M.

---Adapt to scenarios: Suitable for situations with large amounts of data (tens of thousands of tuples). It is best that the column object after ORDER BY is the primary key or unique, so that the ORDER BY operation can be eliminated using the index but the result set is stable ( For the meaning of stable, see method 1).

---Reason: Index scanning will be very fast. But MySQL's sorting operation only has ASC and no DESC (DESC is fake, real DESC will be done in the future, look forward to it).

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

---Statement style: In MySQL, the following methods are available:

PREPARE stmt_name FROM SELECT * FROM table name WHERE id_pk > (?* ?) ORDER BY id_pk

ASC LIMIT M.

---Adapt to scenarios: large data volume.

---Reason: Index scanning will be very fast. The prepare statement is faster than the general query statement.

Method 5: Using MySQL to support ORDER operations, you can use indexes to quickly locate some tuples and avoid full table scans

---For example: Read the tuples in rows 1000 to 1019 (pk is the primary key/unique key).

---SELECT * FROM your_table WHERE pk>=1000 ORDER BY pk ASC LIMIT 0,20.

Method 6: Use "subquery/connection + index" to quickly locate the tuple, and then read the tuple. The principle is the same as method 5

---For example (id is the primary key/unique key, and the variable is in blue font):

Example of using subquery:

SELECT * FROM your_table WHERE id <=
(SELECT id FROM your_table ORDER
BY id desc LIMIT ($page-1)*$pagesize ORDER BY id desc
LIMIT $pagesize

Utilization connection example:

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

Method 7: Stored procedure class (preferably combined with the above methods 5/6)

---Statement style:

will no longer be given ---Adapt to scenarios: large data volume. The method recommended by the author

---Reason: Encapsulating the operation in the server is relatively faster.

Method 8: Negative method

---Someone on the Internet wrote about using SQL_CALC_FOUND_ROWS. It doesn't make sense, don't imitate it.

Basically, it can be extended to all databases, and the principle is the same. However, method 5 may not be extended to other databases. The premise of promotion is that other databases support ORDER BY operations and can use indexes to directly complete sorting.

The above is the detailed content of Summary of MySQL paging technology. For more information, please follow other related articles on the PHP Chinese website!