Shulou(Shulou.com)05/31 Report--

MySQL how to achieve paging query operation, many novices are not very clear about this, in order to help you solve this problem, the following small series will explain in detail for everyone, there are people who need this can learn, I hope you can gain something.

MySQL Pagination Query

Mode 1:

select * from table order by id limit m, n;

The meaning of this statement is to query m+n records, remove the first m records, and return the last n records. There is no doubt that the query can achieve paging function, but if the value of m is larger, the query performance will be lower (the later the number of pages, the lower the query performance), because MySQL also needs to scan m+n records.

Mode 2:

select * from table where id > #max_id# order by id limit n;

This query returns n records at a time without scanning m records as in mode 1. In the case of large data paging, the performance can be significantly better than mode 1, but the paging query must get a maximum id (or minimum id) of the previous query (previous page) at each query. The problem with this query is that we sometimes have no way to get the maximum id (or minimum id) of the previous query (previous page). For example, if we need to query the data on page 5 on page 3, this query method will be helpless.

Mode 3:

In order to avoid queries that cannot be implemented in mode 2, it is also necessary to use the limit m, n clause. For performance, it is necessary to make the value of m as small as possible. For example, currently on page 3, it is necessary to query page 5, with 10 pieces of data per page. The maximum id of page 3 is #max_id#:

select * from table where id > #max_id# order by id limit 20, 10;

In fact, this query method partially solves the problem of method 2, but if you need to query page 100 or 1000 on page 2, the performance will still be poor.

www.2cto.com

Mode 4:

select * from table as a inner join (select id from table order by id limit m, n) as b on a.id = b.id order by a.id;

This query, like Method 1, may have a large value for m, but because the inner subquery scans only the field id, not the entire table, it performs better than Method 1 queries, and it can solve problems that Method 2 and Method 3 cannot solve.

Mode 5:

select * from table where id > (select id from table order by id limit m, 1) limit n;

This query mode is the same as mode 4, and the field id is also scanned through sub-query, and the effect is the same as mode 4. In terms of performance, Mode 5 performs slightly better than Mode 4 because Mode 5 does not require table association, but rather a simple comparison.

Did reading the above help you? If you still want to have further understanding of related knowledge or read more related articles, please pay attention to the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.