Mysql paging statement Limit usage

1. Mysql limit usage when we use a query statement, we often need to return the first few or a few rows of data in the middle. What should we do at this time? Don't worry, mysql already provides us with such a function. SQL code SELECT * FROMtableLIMIT [offset,] rows | rowsOFFSEToffsetLIMIT clause can be used for strong

1. Mysql limit usage when we use a query statement, we often need to return the first few or a few rows of data in the middle. What should we do at this time? Don't worry, mysql already provides us with such a function. SQL code SELECT * FROM table LIMIT [offset,] rows | the rows OFFSEToffset LIMIT clause can be used for strong

1. Mysql limit usage

When we use a query statement, we often need to return the first few or a few rows of data in the middle. What should we do at this time? Don't worry,MysqlThis feature has been provided for us.

SQL code

1. SELECT * FROM table LIMIT [offset,] rows | rows 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. The parameter must be an integer constant. If two parameters are specified, the first parameter specifies the offset of the first returned record row, and the second parameter specifies the maximum number of returned record rows. The OFFSET of the initial record row is 0 rather than 1. To be compatible with PostgreSQL, MySQL also supports Syntax: LIMIT # OFFSET #.

SQL code

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

【Reference, passers-by B: detailed usage of limit in Mysql]

2. Performance Analysis of Mysql paging query statements

For MySql paging SQL statements, the LIMIT Syntax of MySQL is much more elegant than the TOP Syntax of MSSQL. It is natural to use it for paging.

2.1 Basic paging method:

SQL code

2. SELECT... FROM... WHERE... order by... LIMIT...

In the case of small data volumes, such SQL statements are enough. The only problem that needs to be noted is that indexes are used:

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

SQL code

1. SELECT * FROM articles WHERE category_id = 123 order by id LIMIT 50, 10

2.2 subquery paging:

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

SQL code

1. SELECT * FROM articles WHERE category_id = 123 order by id LIMIT 10000, 10

In a word, the more paging goes, the larger the offset of the LIMIT statement, and the slower the speed.

In this case, we can use subqueries to improve paging efficiency, which is 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 10

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;

In my tests, the efficiency of join paging and subquery paging is basically at the same level, and the consumed time is basically the same.

Explain SQL statement:

Id select_type table type possible_keys key key_len ref rows Extra

1 PRIMARY System NULL 1

1 PRIMARY t1 range PRIMARY 4 NULL 6264 Using where

2 DERIVED content index null primary 4 NULL 27085 Using index

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

Why? Because subqueries are completed on indexes, while normal queries are completed on data files, index files are usually much smaller than data files, therefore, the operation will be more efficient.

In practice, we can use a method similar to the policy mode to process pages. For example, if the number of pages is less than one hundred, we use the most basic paging method. If the number of pages is greater than one hundred, we use the paging method of subquery.

[Reference Original article, energy1010 space: MySql paging SQL statement]

3. Oracle paging query statement

Oralce Database

Retrieve N records from the M records in the database table

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, to retrieve 20 records from the table Sys_option (primary key: sys_id) starting from the first 10th records, the statement is as follows:

SQL code

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

3. MSSQLSERVER paging query statement

SQL Server uses the SELECT TOP statement paging. For details, refer

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

Paging solution 1: (use Not In and select top pages)

Statement format:

SQL code

1. Select top 10 *
2. FROM TestTable
3. WHERE (ID NOT IN
4. (Select top 20 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 * Page 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 * Page id
5. FROM table
6. Order by id) as t ))
7. ORDER BY ID

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

Paging solution 2: (use the ID greater than the number and select top pages)

Statement format:

SQL code

1. Select top 10 *
2. FROM TestTable
3. WHERE (ID>
4. (Select max (id)
5. FROM (select top 20 id
6. FROM TestTable
7. Order by id) as t ))
8. ORDER BY ID

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

Paging solution 3: (using SQL cursor Stored Procedure paging)

SQL code

1. Create procedure XiaoZhengGe
2. @ Sqlstr nvarchar (4000), -- query string
3. @ Currentpage int, -- page N
4. @ Pagesize int -- number of lines per page
5. As
6. Set nocount on
7. Declare @ P1 int, -- P1 is the cursor id
8. @ Rowcount int
9. Exec sp_cursoropen @ P1 output, @ sqlstr, @ scrolopt = 1, @ ccopt = 1, @ rowcount = @ rowcount output
10. Select ceiling (1.0 * @ rowcount/@ pagesize) as total number of pages --, @ rowcount as total number of rows, @ currentpage as current page
11. Set @ currentpage = (@ currentpage-1) * @ pagesize + 1
12. Exec sp_cursorfetch @ P1, 16, @ currentpage, @ pagesize
13. Exec sp_cursorclose @ P1
14. Set nocount off

Other solutions: If there is no primary key, you can use a temporary table or solution 3, but the efficiency is low.

We recommend that you add primary keys and indexes during optimization to improve query efficiency.

The SQL query Analyzer displays a comparison: My conclusion is:

Paging solution 2: (using more than ID and select top pages) is the most efficient. You need to splice an SQL statement

Paging solution 1: (using Not In and select top pages) The efficiency is second, and SQL statements need to be spliced.

Paging solution 3: (using SQL cursor Stored Procedure paging) The efficiency is the worst, but the most common

In actual situations, specific analysis is required.

[Reference: Paging query using SQL statements in SQL Server]