Mysqllimit query optimization method (offset) _ MySQL

When a website reaches a certain scale, various website optimizations are required. In the optimization of websites, database optimization is the most important. Optimization of limit statements in mysql databases. Generally, limit is used on pages of paging programs. When an application reaches a certain scale, various website optimizations are required. In the optimization of websites, database optimization is the most important.

Optimization of limit statements in mysql databases.

Generally, limit is used on the page of The paging program. when the application data volume is small enough, it may not feel any problems with the limit statement. However, when the query data volume reaches a certain level, the performance of limit will drop sharply. This is the conclusion of a large number of instances.

Take 10 data records for the same table in different places: 1) The offset is relatively small.

Code example: select * from user limit 10, 10; this SQL statement is run multiple times, and the time is between 0.0004 and 0.0005.

Code example: select * from user where uid> = (select uid from user order by uid limit 10, 1) limit 10; this SQL statement is run multiple times, and the duration is between 0.0005 and 0.0006, mainly 0.0006. Conclusion: The offset is relatively small, and the direct use of limit is better. This is obviously the cause of subquery.

2) large offset

Code example: select * from user limit, 10; this SQL statement is run multiple times, and the time is kept at around 0.0187

Code example: select * from user where uid> = (select uid from user order by uid limit, 1) limit 10; this SQL statement is run multiple times, and the time is kept at around 0.0061, only 1/3 of the former. It can be predicted that the larger the offset, the higher the latter.

Through the comparison above, we can get the mysql limit query statement optimization experience: when using the limit statement, you can directly use limit when the data volume offset is small. when the data volume offset is large, you can use subqueries to optimize the performance.