Summary of basic statement optimization principles in Mysql

Summary of basic statement optimization principles in Mysql

Basic mysql statement Optimization Principles

I. Avoid Column Operations as much as possible, which will cause index failure. Summary of the basic statement optimization principles in Mysql

Select * from t where YEAR (d) >=2011;

Optimized

Select * from t where d> = '2014-2011'

2. When using JOIN, a small result set should be used to drive a large result set, and complicated JOIN queries should be split into multiple queries because multiple tables can be joined, more locks and congestion may occur.

3. Avoid using % when using LIKE

4. select specified query fields. Do not check all fields to save memory usage.

5. Use batch insert statements to save interactions

6. When the limit base is large, between and between limits are faster than limit, but between also has defects. If there is a broken line in the id or the middle part of the id is not read, less data

Select * from t where 1 limit 100000,10

Optimized

Select * from t where id between 100000 and 100010

7. Do not use the rand function to retrieve multiple random records.

8. Avoid NULL

9. Do not use count (id) instead of count (*)

10. do not perform unnecessary sorting operations, but try to complete sorting in the index.