MySQL limit performance analysis and optimization

1. Conclusion

Syntax: limit offset, rows

Conclusion: Under the same row conditions, the larger the offset value, the worse the limit statement performance

2. Testing

Execution test: 5750000 data

sql 1 execution time:

SQL 6 execution time:

3. Optimization

Method 1: You can locate the data location based on other index fields such as the primary key ID, and then use limit 0, rows

For example: select * from gift_record_0 where id >= 43611207 limit 1000 0.14 s

Method 2 (based on business needs): For businesses that do not need to display all data, such as only displaying the first 2,000 data items, limit 0, 2000 can meet this requirement.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links

You may also be interested in:

Solution to data duplication when using limit+order by in MySql paging
Why does MySQL paging become slower and slower when using limit?
MySQL optimization query_cache_limit parameter description
Detailed explanation of the pitfalls of mixing MySQL order by and limit
Reasons and optimization solutions for slow MySQL limit paging with large offsets
Mysql sorting and paging (order by & limit) and existing pitfalls
MySQL uses limit to implement paging example method
How to use MySQL limit and solve the problem of large paging
A brief discussion on the performance issues of MySQL paging limit
Analysis of the Poor Performance Caused by Large Offset of LIMIT in MySQL Query
Why does using limit in MySQL affect performance?

<<: Nginx high concurrency optimization practice

>>: Echarts Basic Introduction: General Configuration of Bar Chart and Line Chart