Performance Analysis and Optimization of MySQL limit

I. conclusion

Syntax structure: limit offset, rows

Conclusion: under the same condition of rows, the higher the value of offset, the worse the performance of limit statement.

Second, testing

Perform tests: 5750000 pieces of data

Sql 1 execution time:

Sql 6 execution time:

III. Optimization

Method 1: you can locate the data location according to 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.14s

Method 2 (according to business requirements): for businesses that do not need to display all the data, such as showing only the first 2000 pieces of data, limit 0, 2000 can meet.

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support. If you want to know more about it, please see the relevant links below.