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

This article will explain the example of mysql paging query for you in detail. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

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

Mysql provides paging capabilities:

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 case, it is recommended to establish a composite index in column and id.

In the above two cases, such sql is sufficient for paging queries with small amounts of data. But for data tables with a level of more than a million, if you use the sql above, the more the offset of the limit statement becomes larger and larger, the slower the query will be. Similar to:

Select * from `user`where `cate` = 'Shaanxi' order by id limit 10000010

In order to avoid this kind of query, we can improve the query efficiency by means of subquery.

Select * from `user` where id > = (select * from `user`user` where `cate` = 'Shaanxi' order by id limit 100000Jue 1) and `cate` = 'Shaanxi' limit 10

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

Direct limit query: typepossible_keyskeykey_lenrefrowsExtraALL (NULL) (NULL) 4076607 subquery paging query: typepossible_keyskeykey_lenrefrowsExtraPRIMARYrangePRIMARYPRIMARY42038331Using whereSUBQUERYindex (NULL) PRIMARY44076663Using index

As you can see, through the way of subquery, the subquery is carried out on the index, while the ordinary query is carried out on the data file. Generally speaking, index files are much smaller than data files, so manipulating index files is more direct and efficient.

In addition, you can also use join paging

SELECT * FROM `user` AS T1 JOIN (SELECT id FROM `user` ORDER BY id LIMIT 100000, 1) AS T2 WHERE t1.id

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.