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