What are the principles of basic sentence optimization in Mysql

This article will share with you what are the principles of basic sentence optimization in Mysql. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Mysql basic sentence optimization principle

First, try to avoid computing on columns, which will lead to index failure.

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

Optimize to

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

2. When using JOIN, you should use small result sets to drive large result sets, and split complex JOIN queries into multiple query, because JOIN multiple tables may lead to more locking and blocking.

3. Avoid using%% when using LIKE

4. Select specifies the query fields. Do not check them all to save memory.

Fifth, use bulk insert statements to save interaction

6. When the cardinality of limit is relatively large, using between,between to qualify is faster than limit, but between also has defects. If there is a line break in the middle of id or the middle part of id does not read, there will be less data.

Select * from t where 1 limit 100000Jing 10

Optimize to

Select * from t where id between 100000 and 100010

Do not use the rand function to fetch multiple random records

Avoid using NULL

Do not use count (id), but count (*)

Do not do unnecessary sorting operations, but should complete the sorting in the index as much as possible

Thank you for reading! This is the end of this article on "what are the principles of basic sentence optimization in Mysql". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!