Optimization method of MySQL to limit query statement

When our website reaches a certain size, various optimizations of the website are necessary. And the website optimization, for the database optimization is the most important. The following authors share the limit statement optimization with the MySQL query in the MySQL database.

It is well known that limit 1 is used for paging applications. When the amount of data in your application is small enough, you may not feel any problems with the limit statement, but when the query volume reaches a certain level, the performance of limit drops sharply. This is a result of a number of examples.

The following are specific examples to illustrate. Here are 10 pieces of data taken from the same table in different places:
(1) When offset was relatively small

select * from user limit 10,10;

This SQL statement is run multiple times between 0.0004 and 0.0005.

Select * From user Where uid >=( Select uid From user Order By uid limit 10,1 ) limit 10;

This SQL statement is run multiple times, staying between 0.0005 and 0.0006, mainly 0.0006.
Conclusion: When migration offset is small, it is better to use limit directly. This is obviously the reason for the subquery.
(2) When offset was young

select * from user limit 10000,10;

This SQL statement is run multiple times, staying around 0.0187

Select * From user Where uid >=( Select uid From user Order By uid limit 10000,1 ) limit 10;

This SQL statement is run multiple times and takes about 0.0061 seconds, about a third of the time. You can expect the larger the offset, the better the latter.

After the above comparison, we can draw a conclusion that when using limit statement, limit can be used directly when the data offset is small; when the data offset is large, we can appropriately use subquery to do relevant performance optimization.