Mysql paging statement Limit usage

1. limit usage of Mysql

When we use query statements, we often need to return the first few or some middle rows of data. What should we do at this time? never mind, mysql We have such a function.

Sql code

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

The LIMIT clause can be used to force a SELECT statement to return the specified number of records. LIMIT accepts one or two numeric parameters. Argument must be an integer constant. Given two parameters, the first specifies the offset of the first return record row and the second specifies the maximum number of return record rows. The offset of the initial record line is 0 (not 1): in order to be compatible with PostgreSQL, MySQL also supports the syntax: LIMIT offset.

Sql code

mysql> SELECT * FROM table LIMIT 5,10; // Retrieving record lines 6-15  
  
//In order to retrieve all record lines from an offset to the end of the Recordset, you can specify the second parameter as - 1:   
mysql> SELECT * FROM table LIMIT 95,-1; // Retrieve record line 96 last  
  
//If only one parameter is given, it indicates that the maximum number of record rows is returned:   
mysql> SELECT * FROM table LIMIT 5; //Retrieve the first 5 record lines  
  
//In other words, LIMIT n is equivalent to LIMIT 0,n.  

    [Quote, passerby B: a detailed explanation of the usage of limit in Mysql]

2. Performance analysis of Mysql paging query statement

Compared with the TOP syntax of MSSQL, MySQL's LIMIT syntax is much more elegant. It's natural to use it for paging.

2.1 basic paging mode:

Sql code

SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT ...  

In the case of small and medium amount of data, such SQL is enough. The only problem to be noted is to ensure that the index is used:

For example, if the actual SQL is similar to the following statement, then in category_ It is better to establish a composite index on the ID and ID columns:

Sql code

SELECT * FROM articles WHERE category_id = 123 ORDER BY id LIMIT 50, 10 

2.2 paging method of sub query:

As the amount of data increases, the number of pages will increase. The SQL of the next few pages may be similar:

Sql code

SELECT * FROM articles WHERE category_id = 123 ORDER BY id LIMIT 10000, 10 

In a word, the more pages are paged back, the more offset the LIMIT statement will have, and the speed will obviously slow down.

At this time, we can improve paging efficiency by sub query, which is roughly as follows:

Sql code

SELECT * FROM articles WHERE  id >=  
 (SELECT id FROM articles  WHERE category_id = 123 ORDER BY id LIMIT 10000, 1) LIMIT 10  

2.3 JOIN pagination method

Sql code

SELECT * FROM `content` AS t1   
JOIN (SELECT id FROM `content` ORDER BY id desc LIMIT ".($page-1)*$pagesize.", 1) AS t2   
WHERE t1.id <= t2.id ORDER BY t1.id desc LIMIT $pagesize;   

After my test, the efficiency of join paging and sub query paging is basically at the same level, and the time consumed is basically the same.

explain SQL statement:

id select_type table type possible_keys key key_len ref rows Extra

1 PRIMARY <derived2> system NULL NULL NULL NULL 1  

1 PRIMARY t1 range PRIMARY PRIMARY 4 NULL 6264 Using where

2 DERIVED content index NULL PRIMARY 4 NULL 27085 Using index

Why is that? Because the sub query is completed on the index, and the common query is completed on the data file. Generally speaking, the index file is much smaller than the data file, so the operation will be more efficient.

In fact, we can use similar policy mode to process paging. For example, if it is less than 100 pages, the most basic paging mode will be used. If it is more than 100 pages, the subquery paging mode will be used.

[quoting the original, Space of energy1010: MySql paging sql statement]

3. Oracle paging query statement

Oracle Database

Retrieve N records from the M record in the database table

Sql code

SELECT * FROM (SELECT ROWNUM r,t1.* From Table name t1 where rownum < M + N) t2   
where t2.r >= M   

For example, from table Sys_option (the primary key is sys_id) to retrieve 20 records from the 10th record, as follows:

Sql code

SELECT * FROM (SELECT ROWNUM R,t1.* From Sys_option where rownum < 30 ) t2   
Where t2.R >= 10   

3. MSSQLSERVER paging query statement

SQL Server mainly uses SELECT TOP statement for paging. For specific scheme, please refer to

Paging scheme I: (paging with Not In and SELECT TOP)

Statement form:

Sql code

SELECT TOP 10 *   
FROM TestTable   
WHERE (ID NOT IN   
(SELECT TOP 20 id   
FROM TestTable   
ORDER BY id))   
ORDER BY ID   

Sql code

SELECT TOP Page size *   
FROM TestTable   
WHERE (ID NOT IN   
(SELECT TOP Page size*the number of pages id   
FROM surface   
ORDER BY id))   
ORDER BY ID   
SELECT TOP Page size *  

Sql code

SELECT TOP Page size *  
FROM TestTable   
WHERE (ID >   
(SELECT MAX(id)   
FROM (SELECT TOP Page size*the number of pages id   
FROM surface   
ORDER BY id) AS T))   
ORDER BY ID   

Paging scheme 2: (paging with ID greater than or SELECT TOP)

Statement form:

Sql code

SELECT TOP 10 *   
FROM TestTable   
WHERE (ID >   
(SELECT MAX(id)   
FROM (SELECT TOP 20 id   
FROM TestTable   
ORDER BY id) AS T))   
ORDER BY ID   

Paging scheme 3: (paging with cursor stored procedure of SQL)

Sql code

create procedure XiaoZhengGe   
@sqlstr nvarchar(4000), --Query string   
@currentpage int, --The first N page   
@pagesize int --Lines per page   
as   
set nocount on   
declare @P1 int, --P1 It's cursorial id   
@rowcount int   
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output   
select ceiling(1.0*@rowcount/@pagesize) as PageCount --,@rowcount as Total number of lines,@currentpage as Current page   
set @currentpage=(@currentpage-1)*@pagesize+1   
exec sp_cursorfetch @P1,16,@currentpage,@pagesize   
exec sp_cursorclose @P1   
set nocount off   

Other schemes: if there is no primary key, you can use temporary tables or scheme 3, but the efficiency will be low.  

When it is recommended to optimize, the query efficiency will be improved by adding the primary key and index.  

 

Through SQL query analyzer, display comparison: my conclusion is:

Paging scheme 2: (using ID greater than how many and SELECT TOP paging) is the most efficient, and SQL statements need to be spliced

Paging scheme 1: (using Not In and SELECT TOP paging) is the second most efficient, and SQL statements need to be spliced

Paging scheme 3: (paging with SQL cursor stored procedures) is the most inefficient, but the most common

 

In the actual situation, it is necessary to make a concrete analysis.  

[reference: Paging query by SQL statement in SQL Server]

Tags: Database SQL MySQL Oracle

Posted on Thu, 21 May 2020 01:00:12 -0700 by jpopuk