Database Application Optimization

Source: Internet
Author: User
10 principles for basic statement optimization: Principle 1: Avoid Column Operations as much as possible, which will cause index failure. For example: select * fromtwhereyear (d) 2011; optimized to: select * fromtwhered2011-01-01; principle 2: When Using JOIN, a small result set should be used to drive a large result set. Meanwhile, the complex JOIN query is split into multiple

10 principles for basic statement optimization: Principle 1: Avoid Column Operations as much as possible, which will cause index failure. Example: select * from t where year (d) = 2011; optimized to: select * from t where d =; principle 2: When Using JOIN, A small result set should be used to drive a large result set. Meanwhile, the complex JOIN query is split into multiple

10 basic statement optimization principles:

Principle 1: Avoid Column Operations as much as possible, which will cause index failure.

For example:

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

Optimized:

Select * from t where d> = '2017-01-01 ';

Principle 2: When Using JOIN, a small result set should be used to drive a large result set. Meanwhile, the complex JOIN Query is split into multiple queries. Because multiple JION tables may cause more locking and blocking.

Principle 3: Pay attention to the use of like fuzzy queries to avoid %.

For example:

Select * from t where name like '% de % ';

Optimized:

Select * from t where name> = 'de' and name <'df ';

Principle 4: only list the fields to be queried. This will not significantly affect the speed, and the main consideration is to save memory.

For example:

Select * from t;

Optimized:

Select id, name from t;

Principle 5: Use batch insert statements to save interactions.

After optimization: insert into t (id, name) values (1, 'A'), (2, 'B'), (3, 'C ');

Principle 6: use between when the LIMIT technology is relatively large.

For example:

Select * from t as t order by id limit 0, 10;

After optimization:

Select * from t as t where id between 1000000 and 1000010 order by id;

Principle 7: Do not use the rand function to obtain multiple random records.

For example:

Select * from t order by rand () limit 20;

Principle 8: Avoid NULL.

Principle 9: Do not use count (id), but count (*);

Principle 10: do not perform unnecessary sorting operations, but probably sort the indexes.

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.