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

Version numbers in css and js links in HTML (refresh cache)

background Search the keyword .htaccess cache in ...

JavaScript custom plug-in to implement tab switching function

This article shares the specific code of JavaScri...

Xftp download and installation tutorial (graphic tutorial)

If you want to transfer files between Windows and...

JavaScript canvas to achieve mirror image effect

This article shares the specific code for JavaScr...

Detailed explanation of basic syntax and data types of JavaScript

Table of contents Importing JavaScript 1. Interna...

HTML version declaration DOCTYPE tag

When we open the source code of a regular website...

Centos8 (minimum installation) tutorial on how to install Python3.8+pip

After minimizing the installation of Python8, I i...

How to use not in to optimize MySql

Recently, when using select query in a project, I...

Vue project implements file download progress bar function

There are two common ways to download files in da...

Table setting background image cannot be 100% displayed solution

The following situations were discovered during d...

Analysis of idea compiler vue indentation error problem scenario

Project scenario: When running the Vue project, t...

Win10 + Ubuntu 16.04 dual system perfect installation tutorial [detailed]

Be sure to remember to back up your data, it is p...

How to Fix File System Errors in Linux Using ‘fsck’

Preface The file system is responsible for organi...