MySQL uses limit to implement paging example method

1. Basic implementation of limit

In general, the client queries the data in the database by paging by passing two parameters: pageNo (page number) and pageSize (number of entries per page). When the amount of data is small (hundreds or thousands of tuples), use MySQL's built-in limit to solve this problem:

Received from client {pageNo:1,pagesize:10} 
select * from table limit (pageNo-1) * pageSize, pageSize;

Received from client {pageNo:5,pageSize:30} 

select * from table limit (pageNo-1) * pageSize,pageSize;

2. Create a primary key or unique index

When the amount of data is small, simply using limit to paging data will not cause noticeable slowdown in performance. However, when the amount of data reaches tens of thousands to millions, the performance of SQL statements will affect the return of data. At this time, you need to use the primary key or unique index to paginate the data;

Assume the primary key or unique index is good_id 
Received from client {pageNo:5,pagesize:10} 
select * from table where good_id > (pageNo-1) * pageSize limit pageSize; 
–Return data with good_id between 40 and 50

3. Re-ranking based on data

When the information needs to be returned in ascending or descending order, re-sort the above statements based on the data. order by ASC/DESC order or reverse order by default

select * from table where good_id > (pageNo-1)*pageSize order by good_id limit pageSize; 
–Return data with good_id between 40 and 50, and sort the data by good_id

4. Best Practices for Paging

Display 10 items per page:
Currently 118 120, 125

In reverse order:
      Size 980 970 7 6 6 5 54 43 32

21 19 98   
Next page:

  select 
    * 
  from 
    tb1 
  where 
    nid < (select nid from (select nid from tb1 where nid < current page minimum value order by nid desc limit data per page * [page number - current page]) A order by A.nid asc limit 1) 
  order by 
    nid desc 
  limit 10;



  select 
    * 
  from 
    tb1 
  where 
    nid < (select nid from (select nid from tb1 where nid < 970 order by nid desc limit 40) A order by A.nid asc limit 1) 
  order by 
    nid desc 
  limit 10;


Previous page:

  select 
    * 
  from 
    tb1 
  where 
    nid < (select nid from (select nid from tb1 where nid > current page maximum value order by nid asc limit data per page * [current page - page number]) A order by A.nid asc limit 1) 
  order by 
    nid desc 
  limit 10;


  select 
    * 
  from 
    tb1 
  where 
    nid < (select nid from (select nid from tb1 where nid > 980 order by nid asc limit 20) A order by A.nid desc limit 1) 
  order by 
    nid desc 
  limit 10;

The above is all about the method of implementing paging in MySQL introduced this time. Thank you for your learning and support for 123WORDPRESS.COM.

You may also be interested in:

Solution to data duplication when using limit+order by in MySql paging
Why does MySQL paging become slower and slower when using limit?
MySQL optimization query_cache_limit parameter description
Detailed explanation of the pitfalls of mixing MySQL order by and limit
Reasons and optimization solutions for slow MySQL limit paging with large offsets
Mysql sorting and paging (order by & limit) and existing pitfalls
How to use MySQL limit and solve the problem of large paging
A brief discussion on the performance issues of MySQL paging limit
MySQL limit performance analysis and optimization
Analysis of the Poor Performance Caused by Large Offset of LIMIT in MySQL Query
Why does using limit in MySQL affect performance?

<<: Summary of Linux file basic attributes knowledge points

>>: A brief analysis of event bubbling and event capture in js