MySQL's "Limit" operation

Limit operation:

Select *  from Table 5,ten;   #返回第6-Select*fromtable5;       Select *  from Table 0,5;    #返回前5行

Performance Optimization:based on the high performance of limit in MySQL5.0, I have a new understanding of data paging.
Test SQL statement 1:

Select *  from Cyclopedia Where ID>=(  SelectMax(ID)   from (select
   
    from
    Order
    by
    90001
    
    
    as;
   

Test SQL Statement 2:

Select *  from Cyclopedia Where ID>=(  SelectMax(ID)   from (select
   
    from
    Order
    by
    90000,
    1
    
    
    as;
   

Test Description:The same is taken 90,000 after 100 records, the 1th sentence fast or the 2nd sentence fast?
The 1th sentence is to take the first 90,001 records, take one of the largest ID value as a starting mark, and then use it to quickly locate the next 100 records
The 2nd choice is only to take 90,000 records after 1, and then take the ID value as the starting point to locate the next 100 records test Result-time:
The 1th sentence executes the result. + Rows in Set (0.23) sec
The 2nd sentence executes the result. + Rows in Set (0.19) sec

It is clear that the 2nd sentence wins.It seems that limit doesn't seem to be exactly what I thought it would be. The full table scan returns the limit offset+length record, so it seems that the limit is higher than the top performance of Ms-sql.

In fact, the 2nd sentence can be simplified into

Select *  from Cyclopedia Where ID>=(  Selectfrom90000,1;

Explanation :
　　 1.Direct use of the ID of the No. 90000 record, do not go through the max operation, so the theoretical efficiency is higher, but in actual use almost do not see the effect, because its own location ID returned is 1 records, Max does not have to work to get results, but this write clearer clarity, save the painting snake that foot.

　　 2.However, since MySQL has limit can directly control the location to take out records, why not simply use SELECT * FROM Cyclopedia limit 90000,1? Wouldn't it be more concise?
This is wrong, try to know, the result is: 1 row in Set (8.88) sec, what, scary enough, reminds me of yesterday in 4.1 than this has a "high score." 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. The above 2 kinds of pagination is much better than the 1 sentence, although it looks like the number of queries more, but in fact, at a small price for efficient performance, is very worthwhile.

　　 3.The 1th option is also available for ms-sql, and may be the best. Because it is always quickest to locate the starting segment by the primary key ID.
top of Ms-sql:

Select Top  - *  fromCyclopediaWhereId>=(Select Top 90001 Max(ID) from(SelectId fromCyclopediaOrder  byID) astmp)

Top Introduction:

However, whether the implementation is a storage process or direct code, the bottleneck is always that the top of the ms-sql is always going to return the top N records, which is not very deep when the amount of data is small, but if hundreds of million, efficiency will certainly be low. In contrast, MySQL's limit has a lot of advantages. Test SQL statement 1:

Select  from 90000

Test SQL Statement 2:

Select  from 90000,1

test Result-time:
90000 rows in Set (0.36) sec
1 row in Set (0.06) sec
and Ms-sql can only use

Select Top 90000  from Cyclopedia

execution time is 390ms and the same operation time is less than MySQL 360ms.This article originates from:http://www.zhenhua.org/article.asp?id=200

MySQL's "Limit" operation