Mysql Paging Statement Limit usage

Reprinted from: http://qimo601.iteye.com/blog/16347481, MySQL limit usage

When we use query statements, we often have to return the first few or the middle of a few rows of data, this time what to do? Don't worry,MySQL has provided us with such a feature.

SQL code

1. SELECT * from table LIMIT [offset,] rows | rows Offset offset

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

SQL code

1. Mysql> SELECT * from table LIMIT 5, 10;//retrieve record line 6-15
3. In order to retrieve all the record rows from an offset to the end of the recordset, you can specify a second parameter of-1:
4. Mysql> SELECT * from table LIMIT 95,-1;//retrieve record line 96-last.
6. If only one parameter is given, it indicates the maximum number of record rows returned:
7. Mysql> SELECT * from table LIMIT 5;//Retrieving the first 5 rows of records
9. In other words, limit n is equivalent to limit 0,n.

" Quote, Passerby B: the usage of limit in MySQL"

2. Performance analysis of MySQL's paging query statement

MySQL paging SQL statement, if compared with MSSQL's top syntax, then MySQL's limit syntax to appear a lot more elegant. It's a natural thing to use it for paging.

2.1 Most basic paging methods:

SQL code

2. SELECT ... From ... WHERE ... ORDER by ... LIMIT ...

In the case of small amounts of data, such SQL is sufficient, and the only problem to be aware of is that the index is used:

For example, if the actual SQL is similar to the following statement, it is better to create a composite index on the category_id, id two column:

SQL code

1. SELECT * from articles WHERE category_id = 123 ORDER by ID LIMIT ten

2.2 Sub-Query paging method:

As the amount of data increases, the number of pages is increasing, and looking at the next few pages of SQL can be similar:

SQL code

1. SELECT * from articles WHERE category_id = 123 ORDER by ID LIMIT 10000,

Word, the more backward the page, the offset of the limit statement will be greater, the speed will be significantly slower.

At this point, we can improve paging efficiency by sub-query, roughly as follows:

SQL code

1. SELECT * from articles WHERE ID >=
2. (SELECT ID from articles WHERE category_id = 123 ORDER by ID limit 10000, 1) limit

2.3JOIN Paging mode

SQL code

1. SELECT * from ' content ' as T1
2. JOIN (SELECT ID from ' content ' ORDER by ID desc LIMIT .) ( $page-1) * $pagesize. ", 1) as T2
3. WHERE t1.id <= t2.id ORDER by t1.id desc LIMIT $pagesize;

After my testing, the efficiency of join paging and sub-query paging is basically on one level, and the time consumed is basically the same.

Explain SQL statements:

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 subqueries are done on the index, and ordinary queries are done on a data file, the index file is generally much smaller than the data file, so it is more efficient to operate.

You can actually use a similar strategy mode to deal with paging, such as judging if it is within 100 pages, using the most basic paging method, more than 100 pages, then use the sub-query paging method.

"Referencing the original, energy1010 space: MySQL paging SQL statement"

3. Oracle Paging query statement

Oralce Database

Retrieving N records from the database table, starting with article M Records

SQL code

1. SELECT * FROM (select ROWNUM r,t1.* from table name T1 where ROWNUM < M + N) T2
2. where T2.R >= M

For example, from table sys_option (primary key to sys_id), 20 records are retrieved starting from the 10th record, with the following statement:

SQL code

1. SELECT * FROM (select ROWNUM r,t1.* from sys_option where ROWNUM <) T2
2. Where T2. R >= 10

3. MSSQLServer Paged Query statement

SQL Server mainly uses SELECT top statement paging, for specific scenarios, refer to

-------------------------------------

Paging Scenario One: (using not in and select top paging)

Statement form:

SQL code

1. SELECT TOP Ten *
2. From TestTable
3. WHERE (ID not in
4. (SELECT TOP ID
5. From TestTable
6. ORDER by ID))
7. ORDER by ID

SQL code

1. SELECT TOP Page Size *
2. From TestTable
3. WHERE (ID not in
4. (SELECT TOP Page Size * Number of pages ID
5. From table
6. ORDER by ID))
7. ORDER by ID
8. SELECT TOP Page Size *

SQL code

1. From TestTable
2. WHERE (ID >
3. (SELECT MAX (ID)
4. From (SELECT TOP Page Size * Number of pages ID
5. From table
6. ORDER by ID) (as T) )
7. ORDER by ID

-------------------------------------

Paging Scenario Two: (using ID greater than how much and select top paging)

Statement form:

SQL code

1. SELECT TOP Ten *
2. From TestTable
3. WHERE (ID >
4. (SELECT MAX (ID)
5. From (SELECT TOP ID
6. From TestTable
7. ORDER by ID) (as T) )
8. ORDER by ID

-------------------------------------

Paging Scenario Three: (Paging with SQL cursor stored procedures)

SQL code

2. Create procedure Xiaozhengge
4. @sqlstr nvarchar (4000), --query string
6. @currentpage int, --page n
8. @pagesize int --Number of rows per page
10. As
12. SET NOCOUNT on
14. DECLARE @P1 int, --P1 is the ID of the cursor
16. @rowcount int
18. EXEC sp_cursoropen @P1 output, @sqlstr, @scrollopt =1, @ccopt =1,@[email protected] output
20. Select Ceiling (1.0* @rowcount/@pagesize) as total number of pages--, @rowcount as rows, @currentpage as current page
22. Set @currentpage = (@currentpage-1) * @pagesize +1
24. exec sp_cursorfetch @P1, @currentpage, @pagesize
26. EXEC sp_cursorclose @P1
28. SET NOCOUNT off

Other scenarios: If you don't have a primary key, you can use a temporary table, or you can do it with scenario three, but the efficiency will be low.

When tuning is recommended, the query efficiency increases with the primary key and index.

Using SQL Query Analyzer to show comparisons: My conclusion is:

Paging Scenario Two: (using ID greater than how much and select top paging) The most efficient, need to splice SQL statements

Paging Scenario One: (using not and select top paging) second, the need to splice SQL statements

Paging Scenario Three: (Paging with SQL cursor stored procedures) is the least efficient, but the most common

In the actual situation, to be specific analysis.

Mysql Paging Statement Limit usage