Usage of limit in Mysql: When we use query statements, we often need to return the first few or middle rows of data. What should we do at this time? Don't worry, mysql already provides us with such a function.
SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
The LIMIT clause can be used to force a SELECT statement to return a specified number of records. LIMIT accepts one or two numeric arguments. The parameter must be an integer constant. If two parameters are given, 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 (instead of 1): For compatibility with PostgreSQL, MySQL also supports the syntax: LIMIT # OFFSET #.
mysql> SELECT * FROM table LIMIT 5,10; // Retrieve record rows 6-15
//To retrieve all record rows from a certain offset to the end of the recordset, you can specify -1 as the second parameter:
mysql> SELECT * FROM table LIMIT 95,-1; // Retrieve record row 96-last.
//If only one parameter is given, it means returning the maximum number of record rows:
mysql> SELECT * FROM table LIMIT 5; //Retrieve the first 5 record rows
//In other words, LIMIT n is equivalent to LIMIT 0,n.
Note the difference between limit 10 and limit 9,1:
For example:
1.
Select * From cyclopedia Where ID>=(
Select Max(ID) From (
Select ID From cyclopedia Order By ID limit 90001
)As tmp
) limit 100;
2.
Select * From cyclopedia Where ID>=(
Select Max(ID) From (
Select ID From cyclopedia Order By ID limit 90000,1
)As tmp
) limit 100;
Similarly, if we get the next 100 records after 90,000, which one is faster, the first sentence or the second sentence?
The first sentence is to first take the first 90001 records, take the largest ID value as the starting identifier, and then use it to quickly locate the next 100 records. The second sentence is to take only the last 90000 records, and then take the ID value. Use the starting mark to locate the first sentence of 100 records and execute the result. 100 rows in set (0.23) sec
Execution result of sentence 2.100 rows in set (0.19) sec
In fact, the second sentence can be simplified to:
Select * From cyclopedia Where ID>=(
Select ID From cyclopedia limit 90000,1
)limit 100;
Directly use the ID of the 90,000th record without going through the Max operation. This should be more efficient in theory, but in actual use the effect is almost invisible, because the positioning ID returns only 1 record, and Max almost does not need to be run. You can get the result, but writing it this way is clearer and clearer, eliminating the need to draw a snake.
Select Top 100 * From cyclopedia Where ID>=(
Select Top 90001 Max(ID) From (
Select ID From cyclopedia Order By ID
)As tmp
)
But no matter whether it is implemented in a stored procedure or directly in the code, the bottleneck is always that MS-SQL's TOP always returns the first N records. This situation is not deeply felt when the amount of data is not large, but if there are hundreds or thousands of 10,000, the efficiency will definitely be low. In comparison, MySQL's limit has many advantages.
,implement:
Select ID From cyclopedia limit 90000
Select ID From cyclopedia limit 90000,1
The results are:
90000 rows in set (0.36) sec
1 row in set (0.06) sec
MS-SQL can only use Select Top 90000 ID From cyclopedia. The execution time is 390ms, and the execution time of the same operation is not as good as MySQL's 360ms.
The offset of limit is used when there are more records. When there are fewer records, the offset is smaller, so it is better to use limit directly. The larger the offset, the better the latter.
///////////////////////////////////////////////////// ////////////////////////
1. When the offset is relatively small.
select * from yanxue8_visit limit 10,10
Run multiple times, the time remains between 0.0004-0.0005
Select * From yanxue8_visit Where vid >=(
Select vid From yanxue8_visit Order By vid limit 10,1
) limit 10
Run multiple times, the time remains between 0.0005-0.0006, mainly 0.0006
Conclusion: When the offset offset is small, it is better to use limit directly. This display is due to the subquery.
2. When the offset is large.
select * from yanxue8_visit limit 10000,10
Run multiple times, the time remains around 0.0187
Select * From yanxue8_visit Where vid >=(
Select vid From yanxue8_visit Order By vid limit 10000,1
) limit 10
After multiple runs, the time remains at around 0.0061, which is only 1/3 of the former. The larger the preset offset, the better the latter.
///////////////////////////////////////////////////// ////////////////////////////////////////////////
mysql> SELECT * FROM table LIMIT 95,-1; // Retrieve record row 96-last.
//If only one parameter is given, it means returning the maximum number of record rows
This article comes from the CSDN blog. Please indicate the source when reprinting: http://blog.csdn.net/zhqingyun163/archive/2009/12/22/5053579.aspx