The optimization method of Mysql to limit query statement _mysql

When our website reaches a certain scale, the various optimization of the website must be carried out. And the optimization of the website, for the database optimization is the most important. The following author will share with you the optimization of the query statements in the MySQL database about limit statements.

We all know that the general limit is used in pagination of the program page, when your application data volume is small enough, you may not feel the limit statement of any problems, but when the amount of query data to a certain extent, limit performance will drop dramatically. This is the result of a large number of examples.

The following specific case to illustrate, here is the same table in different places to take 10 data:
(1) When offset is relatively small

Copy Code code as follows:

SELECT * from user limit 10, 10;

This SQL statement runs multiple times, keeping the time between 0.0004-0.0005.

Copy Code code as follows:

SELECT * from user Where uid >= (select UID to User order by UID limit 10,1) limit 10;

This SQL statement runs 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 user limit 10000, 10;

This SQL statement runs multiple times, keeping the time at around 0.0187

Copy Code code as follows:

SELECT * from user Where uid >= (select UID to User order by UID limit 10000,1) limit 10;

This SQL statement runs multiple times, keeping the time at around 0.0061, only 1/3 of the former. You can expect the larger the offset, the better the latter.

Through the above comparison, we come to the conclusion that when using the limit statement, when the amount of data offset is small, we can use the limit directly, when the data quantity offset is large, we can use the subquery to do the related performance optimization.