first, the basicThe limit syntax for SQL is in the following form
SELECT * FROM table LIMIT [offset,] rows | rows offset OffsetWhen offset is omitted, offset is treated as 0 to extract the previous rows data from the query and, when Offset>=0, represents the rows data from offset that the query was extracted from, and if rows< 0 means that all data retrieved from offset is extracted from the query, when offset<0, indicates that all data extracted from the query to the rows data is removed, that is, the-rows data between the last row-rows and rows is excluded. If rows is greater than the number of data bars in the actual query, rows are taken as the number of data bars for the actual query.
Second, examplesInstance 1 retrieves the record line 6-15select * from table LIMIT 5, 10; In order to retrieve all the record rows from an offset to the end of the recordset, you can specify that the rows parameter is-1: Instance 2 retrieves the record line 96-lastselect * from table LIMIT 95,-1; If only the rows parameter is given, it represents the previous rows data; in other words, the limit n is equivalent to the Limit 0,n instance 3 to retrieve the first 5 record rows of select * from table LIMIT 5; Instance 4 returns 第6-15 row data select * FROM Table LIMIT 5, 10; or select * from table LIMIT 5; or select * FROM table LIMIT 0, 5; Instance 5 returns 0 to last Row-rows data select * FROM class limit 10,-1 or select * FROM class limit-1 OFFSET 10 If there are 100 data in the class table, the return is 0 start 90 data, i.e. 0 to (100-10)
Third, Performance3.1, basic 1, offset is relatively small time. SELECT * from Yanxue8_visit limit 10, 10 runs multiple times, 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 runs multiple times, The time remains between 0.0005 and 0.0006, mostly 0.0006 conclusions: When offset offsets are smaller, direct use of limit is preferred. This is clearly the cause of the subquery. 2. When the offset is large. SELECT * FROM Yanxue8_visit limit 10000, 10 times run, time remains at around 0.0187 select * from yanxue8_visit Where vid >= ( Select vid from yanxue8_visit Order by vid limit 10000,1) limit 10 runs multiple times, the time remains at around 0.0061, only the former 1/3. You can expect the larger the offset, the better the latter. 3.2, performance optimization: Scenario 1. SELECT * FROM Cyclopedia Where id>= (select Max (ID) from ( select ID from Cyclopedia Order by ID limit 90001) as TM p) limit 100; Scenario 2. SELECT * FROM Cyclopedia Where id>= (Select Max (ID) from ( select id from Cyclopedia Order by ID limit 90 000,1) as TMP) limit 100, also take 90,000 after 100 records, the 1th sentence is fast or the 2nd sentence fast? The 1th scenario is to take the first 90,001 records and take one of the largest ID values as the starting ID. And then use it to quickly locate the next 100 records the 2nd option is to take only 90,000 records after 1, and then take the ID value as the starting point of the positioning of the next 100 records 1th programme execution results. rows in Set (0.23) SEC 2nd program executionThe result. The 0.19 sec is clearly the 2nd option to win. Because the ID here is the primary key, it does not do a full table scan, but instead returns the limit Offset+length record directly, In this way, it seems that limit is higher than the top performance of ms-sql. In fact, the 2nd solution can be simplified into a Select * from Cyclopedia Where id>= (select id from Cyclopedia limit 90000,1) limit 100, directly using the ID of the No. 90000 record, do not go through the max operation, this is more efficient in theory, but in actual use almost do not see the effect, because the location ID itself returned is 1 records, Max doesn't have to work to get results, but it's clearer and clearer, eliminating the need to draw snakes that foot . but since MySQL has limit can directly control the location of the record, why not simply use SELECT * FROM Cyclopedia limit What about 90000,1? It's wrong to think that way, you know, the result is: 1 row in Set (8.88) sec, what is it, scary enough? SELECT * Best not to use, in line with what, choose what principle, select the more fields, the greater the amount of field data, the slower the speed. In addition, in the 2nd scenario, the ID is the primary key, so it does not do a full table scan, but instead returns the limit Offset+length record directly. The 1th option is also available for ms-sql, and may be the best. Because the primary key ID is always the quickest way to locate the starting segment. Select top from Cyclopedia Where id>= (select top 90001 Max ( ID) from (the select ID from the Cyclopedia Order by ID) as TMP) but whether the implementation is a storage process or direct code, the bottleneck is always that the top of ms-sql always returns the top N records, This situation is not felt deep when the amount of data is small, but if millions of million, efficiency will certainly be low. In contrast, MySQL's limit has a lot of advantages, execution: Select ID from Cyclopedia limit 90000Select ID from The results of Cyclopedia limit 90000,1 are: 90000 rows in Set (0.36) SEC1Row in Set (0.06) SEC and ms-sql can only be used with select Top 90000 ID from Cyclopedia execution time is 390ms, performing the same operation time is less than MySQL 360ms.
SQL statement limit drill-down
