MySQL Query optimization: Limit

Limit Syntax:

[offset,| 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 offset of the initial record line is 0 (not 1).
In order to be compatible with PostgreSQL, MySQL also supports syntax: LIMIT # OFFSET #.

eg

Mysql>SELECT*FromTable LIMIT5,10;Retrieving record lines 6-15//to retrieve all record rows from one offset to the end of the recordset, you can specify that the second argument is a -1mysql> select * Span style= "color: #0000ff;" >from 95,-< Span style= "color: #800000;" >1; //retrieves the record row 96-last//if only one parameter is given, which represents the maximum number of record rows returned, in other words, the limit n equals the limit 0,nmysql> select Span style= "color: #808080;" >* from table LIMIT 5; Span style= "color: #008000;" >//retrieving the first 5 records lines                

MySQL's limit brings great convenience to paging, but when the amount of data is large, limit performance drops sharply. also take 10 data, the following two sentences are not a number of levels.

10000,0,ten 

Instead of using limit directly, you first get the ID of offset and then use the limit size directly to get the data. According to his data, it is significantly better to use limit directly.

Here I use the data in two different situations to test.

1, when the offset is relatively small:

10,
Run multiple times with 0.0004-0.0005 time remaining

Where vid >= (10,
Run multiple times, the time remains between 0.0005-0.0006, mainly 0.0006

Conclusion: The direct use of limit is better when the offset is smaller. This is clearly the cause of the subquery.

2. When the offset is large:

Select*From10000,10 Span style= "color: #008000;" >//multiple runs, the time remains around 0.0187 select * from table where vid >= (select vid from table order by vid limit 10000,10//run several times, the time remains around 0.0061, only the former 1/3. You can expect the larger the offset, the better the latter.  

Reprint: http://www.360doc.com/content/11/0224/11/1542811_95659889.shtml

MySQL Query optimization: Limit