How to implement paging query in MySQL

Paging query is implemented in MySQL: When the amount of data is small, limit query can be used to implement paging query. When the amount of data is large, it can be implemented by establishing a primary key or unique index. In addition, it can be sorted by order by.

Admin management is always unavoidable in back-end projects. When back-end management needs to display data, it will need to use paging. Next, in this article, I will share with you how to implement paging query in MySQL, with It serves as a reference and I hope it will be helpful to everyone.

【Recommended course: MySQL Tutorial】

Generally, when performing paging queries in MySQL, limit query will be used, and order by will be used in the query for sorting. Next, we will introduce in detail how MySQL implements paging query

Paging requirements:

The client passes start (page number), limit (number of items displayed on each page) ) two parameters to query the data in the database table by paging. MySql database provides paging functions with limit m, n, but the usage of this function is different from our needs, so you need to rewrite the paging statement that suits you according to the actual situation. . Example

The sql for querying data from items 1 to 10 is:

select * from table limit 0,10;

Corresponding to our needs is to query the data on the first page:

select * from table limit (1-1)*10,10;

From the above From the analysis, we can conclude that the format of paging sql is:

 select * from table limit (start-1)*limit,limit;

where start is the page number and limit is the number of items displayed on each page.

Establishing a primary key or unique index

Using limit for data paging when the data volume is small will not cause significant slowdown in performance, but when the data volume reaches When the level reaches 10,000 or 1,000,000, the performance of SQL statements will affect the return of data. This is to use the primary key or unique index instead of limit for data paging

Example: Return data between 10 and 50

Set the primary key or unique index to demo_id

select * from table where demo_id > (pageNo-1)*pageSize limit pageSize;

Reordering based on data

When the information needs to be returned in order or reverse order, the above data needs to be sorted. Order by ASC can be used to express the order, and order by DESC can be used to express the reverse order. Generally, the default is the order.

Example: The returned data is arranged in the order of demo_id

select * from table where demo_id > (pageNo-1)*pageSize order by demo_id limit pageSize;

Summary: The above is this article The entire content, I hope it will be helpful to everyone

The above is the detailed content of How to implement paging query in MySQL. For more information, please follow other related articles on the PHP Chinese website!