Classic MySQL limit query optimization

The following article is mainly on the MySQL limit query optimization of the specific content, we all know that the MySQL database optimization is very important. The other most common and most desirable optimization is limit. The MySQL limit to paging has brought great convenience, but when the amount of data, limit the performance dropped sharply.

The same is to take 10 data

It is not a quantitative level.

There are also many online optimization guidelines for the limit, which are all translated from the MySQL manual, although correct but impractical. Today found an article written on the limit optimization, very good.

The article is not directly use the limit, but first get the offset id and then directly use the limit size to get the data. According to his data, it is obviously better than using limit directly. Here I use the data in two cases to test. (Test environment win2033 + p4 dual-core (3GHZ) + 4G memory MySQLlimit query)

1, offset relatively small time

Running multiple times, the time remained between 0.0004-0.0005

Running multiple times, the time remained between 0.0005-0.0006, mainly 0.0006

Conclusion: Offset offset smaller, the direct use of limit better. This is obviously the reason for the subquery.

2, offset big time

Running multiple times, the time remained at 0.0187 or so

Running multiple times, the time remained at around 0.0061, only the former one-third. The larger the offset, the better the latter.

After the pay attention to correct their own limit statement, optimize MySQL

Recommended comments

MySQL optimization is very important. The other most common and most needed optimization is limit. The MySQL limit to paging has brought great convenience, but when the amount of data, limit the performance dropped sharply.

The above is related to the optimization of the introduction of MySQLlimit query, hope you can gain something.