Several implementation methods and advantages and disadvantages of SQL paging query in MySQL

【SQL】SQL paging query summary

The need for paging is often encountered during the development process. Let’s summarize it here today.

Simply put, there are two methods: one is to control at the source, and the other is to control at the end. The source control puts the paging logic in the SQL layer; the client control obtains all data at one time and puts the paging logic in the UI (such as GridView). Obviously, on-end control has low development difficulty and is suitable for small-scale data, but the performance and IO consumption become unacceptable when the data volume increases. On-source control is more balanced in terms of performance and development difficulty and is suitable for most business scenarios. In addition, a layer can be added between the source and the end based on objective conditions (performance requirements, resource usage of the source and the end, etc.), and special algorithms and technologies can be used for processing. The following mainly discusses paging on the source, that is, SQL.

The problem of paging is actually to intercept the part that needs to be displayed currently from a pile of ordered data that meets the conditions. In fact, various databases have built-in strategies for paging issues, such as LIMIT in MySql, ROWNUM and ROW_NUMBER() in Oracle, and TOP and ROW_NUMBER() in SqlServer. Based on this, we can get a series of paging methods.

1. Based on MySQL's LIMIT and Oracle's ROWNUM, you can directly limit the return range (taking MySQL as an example, note that subquery should be applied when using Oracle's ROWNUM):

Method 1: Directly limit the return range

SELECT * FROM table WHERE query condition ORDER BY sort condition LIMIT ((page number - 1) * page size), page size;

Advantages: Simple writing.
Disadvantages: When the page number and page size are too large, performance degrades significantly.
Applicable: The amount of data is not large.

2. Based on LIMIT (MySql), ROWNUM (Oracle) and TOP (SqlServer), they can limit the number of rows returned, so we can get the following two sets of general methods (taking SqlServer as an example):

Method 2: NOT IN

SELECT TOP page size * FROM table WHERE primary key NOT IN
(
 SELECT TOP (page number - 1) * page size primary key FROM table WHERE query condition ORDER BY sort condition)
ORDER BY sorting condition

Advantages: strong versatility.
Disadvantages: When the amount of data is large, the page is turned backwards, and too much data in NOT IN will affect performance.
Applicable: The amount of data is not large.

Method 3: MAX

SELECT TOP page size * FROM table WHERE query condition AND id >
(
 SELECT ISNULL(MAX(id),0) FROM 
 (
  SELECT TOP ((page number - 1) * page size) id FROM table WHERE query condition ORDER BY id 
 ) AS tempTable
) 
ORDER BY id

Advantages: Fast, especially when id is the primary key.
Disadvantages: narrow applicability, requires the sorting conditions to be single and comparable.
Applicable: Simple sorting (in special cases, you can also try to convert it into similar comparable values ​​for processing).

3. Based on ROW_NUMBER() of SqlServer and Oracle, the row number of the returned data can be obtained. Based on this, the following method is obtained by limiting the return range (taking SqlServer as an example):

Method 4: ROW_NUMBER()

SELECT TOP page_size * FROM 
(
 SELECT TOP (page number * page size) ROW_NUMBER() OVER (ORDER BY sort condition) AS RowNum, * FROM table WHERE query condition) AS tempTable
WHERE RowNum BETWEEN (page number - 1) * page size + 1 AND page number * page size ORDER BY RowNum

Advantages: It has advantages over NOT IN when the amount of data is large.
Disadvantage: Not as good as NOT IN when the amount of data is small.
Applicable to: most paging query requirements.

The above is my humble opinion. The performance comparison comes from online information and personal judgment. There is no in-depth experiment. Please correct me if there are any inappropriate points.

This is the end of this article about several implementation methods and advantages and disadvantages of paging query in MySQL. For more information about paging query methods in MySQL, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future.