The instance method of paging with limit in MySQL

Time:2020-4-5

1、 Basic implementation of limit

In general, the client queries the data in the database by passing two parameters of pageno (page number) and PageSize (number of entries per page). When the data volume is small (tuple hundred / thousand), the client uses MySQL’s own limit to solve this problem:

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

Received client {pageno: 5, PageSize: 30} 

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

2、 Establish primary key or unique index

When the amount of data is small, the simple use of limit for data paging will not slow down obviously in performance, but the performance of SQL statements with the amount of data reaching 10000 to million will affect the return of data. In this case, primary key or unique index should be used for data paging;

Assume that the primary key or unique index is good u id 
Received client {pageno: 5, PageSize: 10} 
select * from table where good_id > (pageNo-1) * pageSize limit pageSize; 
– return the data with a good Chu ID between 40 and 50

3、 Reorder based on data

When the information to be returned is in order or reverse order, the above statements are reordered based on the data. Order by ASC / desc order or reverse order defaults to order

select * from table where good_id > (pageNo-1)*pageSize order by good_id limit pageSize; 
– return the data with good UU ID between 40 and 50, and arrange the data in order of good UU ID

4、 Paging best

10 items per page:
Current 118 120125

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 < 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 > 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 the whole content of this introduction about MySQL’s paging method. Thank you for your learning and support for developepaer.