Mysql paging query example explanation

The LIMIT clause can be used to force the SELECT statement to return a specified number of records. LIMIT accepts one or two numeric arguments. The parameter must be an integer constant. If two parameters are given, the first parameter specifies the offset of the first returned record row, and the second parameter specifies the maximum number of the returned record row. The offset of the initial record row is 0 (not 1). Below, we summarize mysql paging queries for special cases.

mysql provides the paging function:

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

The simplest usage is:

select * from table limit ?,?

This is the simplest limit paging query. Use with where conditions:

select * from table where column > ? order by id limit ?,?

In the above situation, it is recommended to create a composite index on column and id.
In the above two cases, this SQL is sufficient for paging queries with small amounts of data. But for data tables with millions of levels or more, if you use the above SQL, the offset of the limit statement will become larger and larger, and the query will become slower and slower. Similar to:

select * from `user` where `cate`='陕西' order by id limit 100000,10

In order to avoid this kind of query, we can improve query efficiency through subquery.

select * from `user` where id >=(select * from `user` where `cate`='陕西' order by id limit 100000,1) and `cate`='陕西' limit 10

Through explain we can see the difference between direct limit query and subquery:

Direct limit query:

type	possible_keys	key	key_len	ref	rows	Extra
ALL	(NULL)	(NULL)	(NULL)	(NULL)	4076607

Subquery paging query:

##PRIMARY rangePRIMARYPRIMARY42038331Using whereSUBQUERYindex(NULL)PRIMARY44076663Using index

type	possible_keys	key	key_len	ref	rows	Extra

It can be seen that

through the subquery method, the subquery is performed on the index, while the ordinary query is performed on the data file. Generally speaking, index files are much smaller than data files, so operating index files is more direct and efficient.

In addition, you can also use

join paging method

SELECT * FROM `user` AS t1 
JOIN (SELECT id FROM `user` ORDER BY id LIMIT 100000, 1) AS t2 
WHERE t1.id <= t2.id ORDER BY t1.id LIMIT 10;

The efficiency of join paging and subquery paging are basically at the same level. (However, subqueries need to create temporary tables in memory. After the query is completed, MySQL needs to cancel these temporary tables. This can be avoided by joining) Before paging the query, you can make a judgment. If it is within the limited number of pages, then Use basic paging query, and use subquery paging processing if it is greater than 1.

Related recommendations:

mysql paging performance exploration

MySQL paging optimization test case

MySQL Paging Performance Optimization Guide

The above is the detailed content of Mysql paging query example explanation. For more information, please follow other related articles on the PHP Chinese website!