MySQL uses limit to implement paging example method

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

Recommend

Complete step record of vue encapsulation TabBar component

Table of contents Implementation ideas: Step 1: C...

25 Ways and Tips to Increase Web Page Loading Speed

Introduction <br />Not everyone has access t...

Detailed explanation of client configuration for vue3+electron12+dll development

Table of contents Modify the repository source st...

Ubuntu 20.04 Chinese input method installation steps

This article installs Google Input Method. In fac...

MySql development of automatic synchronization table structure

Development Pain Points During the development pr...

Summary of 6 solutions for implementing singleton mode in JS

Preface Today, I was reviewing the creational pat...

Linux concurrent execution is simple, just do it this way

Concurrency Functions time for i in `grep server ...

How to use mixins in Vue

Table of contents Preface How to use Summarize Pr...

Three.js realizes Facebook Metaverse 3D dynamic logo effect

Table of contents background What is the Metavers...

Analysis of rel attribute in HTML

.y { background: url(//img.jbzj.com/images/o_y.pn...

Solution for creating multiple databases when Docker starts PostgreSQL

1 Introduction In the article "Start Postgre...

How to clear mysql registry

Specific method: 1. Press [ win+r ] to open the r...