SQL optimization suggestion memorandum _ MySQL

Source: Internet
Author: User
SQL optimization suggestion memorandum bitsCN.com

SQL optimization suggestion memorandum

1. SQL comments generally do not increase performance overhead and enhance readability.

2. brief transactions

3. avoid cursor operations

Because the cursor occupies a large amount of resources

4. Balance between IN and EXISTS

5. avoid nested views and avoid pointing to multiple tables during single-table search

6. DISTINCT and order by overhead

It can be processed in the application.

7. try to set the default value for each column, and not null. Because columns containing NULL values cannot use indexes, even if you create an index. Queries that use is null and is not null do NOT use indexes.

8. massive query to avoid type conversion

9. create an index for the columns associated with the foreign key

10. UNION and union all have higher efficiency.

11. INSERT statements

Insert into table VALUES (...) Performance is worse than insert into table (...) VALUES (...)

12. notes for using ORDER

Any non-index item or calculation expression in order by will reduce the query efficiency.

13. Balance BETWEEN and IN

...

BitsCN.com

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.