Limit usage and optimization for MySQL

General usage:

Usage One:

SELECT ' Keyword_rank '. *  from WHERE (Advertiserid='2'1;

For example, this SQL, the limit followed by 2 data, offset is followed by the beginning of the 1th reading.

Usage Two:

SELECT ' Keyword_rank '. *  from WHERE (Advertiserid='2',1;

And this sql,limit is followed by reading from 2nd, reading 1 messages.

Usage Three:

Select *  from < conditional statements >1 ,

Starting from 100th-the last record of the article.

Usage Four:

Select *  from < conditional statements >

Equivalent to limit 0, 15, query results fetch the first 15 data

Usage Five:

MySQL low version does not support limit offset

Limit offset works correctly in MySQL version 4.0 and above and is not available in older versions of MySQL 3.23

Limit m offset n is equivalent to limit m,n

Optimization of limit:

MySQL's limit brings great convenience to paging, but when the amount of data is large, limit performance drops sharply.

The optimization of MySQL is very important. The other most commonly used and most need to optimize is limit. MySQL's limit brings great convenience to paging, but when the amount of data is large, limit performance drops sharply.

Same as 10 data

Select *  from 10000,tenselect*from0,

is not a level of quantity.

There are also many five optimization criteria for limit, which are translated from MySQL manual, although correct but not practical. Today found an article written about limit optimization, very good.

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.

(Test environment CentOS 6+P4 Dual Core (3GHZ) +4g memory MySQL 5.0.19)

1, offset is relatively small time.

Select *  from Ten,ten

Run multiple times with 0.0004-0.0005 time remaining

Select *  from Where vid >=(    SelectfromOrderby10 ,1

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 *  from 10000,ten

Run multiple times and keep time at around 0.0187

Select *  from Where vid >=(    SelectfromOrderby10000 ,1

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.

Reference:

Http://www.jb51.net/article/41759.htm (the above content is transferred from this article)

Http://www.jb51.net/article/85312.htm

Http://www.cnblogs.com/lisqiong/p/5635009.html

http://blog.csdn.net/cug_jiang126com/article/details/42246721

Http://www.cnblogs.com/beynol/p/mysql-optimization-limit.html

MySQL Limit usage and optimization (RPM)