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:
|
<<: Summary of Linux file basic attributes knowledge points
>>: A brief analysis of event bubbling and event capture in js
background Search the keyword .htaccess cache in ...
This article shares the specific code of JavaScri...
If you want to transfer files between Windows and...
Preface Recently, I encountered a requirement at ...
This article shares the specific code for JavaScr...
Table of contents Importing JavaScript 1. Interna...
When we open the source code of a regular website...
After minimizing the installation of Python8, I i...
Recently, when using select query in a project, I...
Since the default Linux kernel parameters are bas...
There are two common ways to download files in da...
The following situations were discovered during d...
Project scenario: When running the Vue project, t...
Be sure to remember to back up your data, it is p...
Preface The file system is responsible for organi...