Use a
Copy Code code as follows:
SELECT ' Keyword_rank '. * from ' Keyword_rank ' WHERE (advertiserid= ' a ') LIMIT 2 OFFSET 1;
For example, the SQL, followed by the limit is 2 data, offset is from the 1th read from the beginning.
Usage Two
Copy Code code as follows:
SELECT ' Keyword_rank '. * from ' Keyword_rank ' WHERE (advertiserid= ') LIMIT 2, 1;
And this sql,limit is followed by the 2nd read, read 1 messages.
Don't confuse these two.
Usage Three
Copy Code code as follows:
SELECT * FROM tablename < conditional statement > limit 100,-1
Start after 100th-record of the last article
Usage Four
Copy Code code as follows:
SELECT * FROM tablename < conditional statement > limit 15
Equivalent to limit 0,15. Query results take the first 15 data usage five
MySQL low version does not support limit offset
Limit offset works well in MySQL version 4.0 and is not available in the older version of MySQL 3.23
Limit m offset n is equivalent to limit m,n
Optimization of Limit
The limit of MySQL brings great convenience to paging, but when the amount of data is large, the performance of limit drops dramatically.
Source: A three-acre blog
The optimization of MySQL is very important. The other most common and most need to optimize is limit. The limit of MySQL brings great convenience to paging, but when the amount of data is large, the performance of limit is reduced dramatically.
It's also taking 10 data.
Copy Code code as follows:
SELECT * FROM Yanxue8_visit limit 10000,10
SELECT * FROM Yanxue8_visit limit 0,10
is not a quantitative level.
There are also a lot of five optimization guidelines on limit, which are translated from MySQL manual, although correct but not practical. Today I found an article to write about limit optimization, very good.
Instead of using limit directly, we first get the ID of offset and then use limit size directly to get the data. According to his data, it is significantly better than using limit directly. Here I use the data in two different situations to test. (Test environment WIN2033+P4 dual core (3GHZ) +4g memory MySQL 5.0.19)
1, offset relatively small time.
Copy Code code as follows:
SELECT * FROM Yanxue8_visit limit 10,10
Run multiple times, keeping the time between 0.0004-0.0005
Copy Code code as follows:
Select * from Yanxue8_visit Where vid >= (
Select vid from Yanxue8_visit order by vid limit 10,1
) Limit 10
Run multiple times, keeping the time between 0.0005-0.0006, mainly 0.0006
Conclusion: The direct use of limit is more excellent when offset is smaller. This is obviously the reason for the subquery.
2. When the offset is big.
Copy Code code as follows:
SELECT * FROM Yanxue8_visit limit 10000,10
Run more than once and keep the time around 0.0187
Copy Code code as follows:
Select * from Yanxue8_visit Where vid >= (
Select vid from Yanxue8_visit order by vid limit 10000,1
) Limit 10
Run several times, the time remained at about 0.0061, only the former 1/3. You can expect the larger the offset, the better the latter.
Later should pay attention to correct their own limit statements, optimize the MySQL
