Database Tuning Tutorial (12) Optimizing SQL statements

Source: Internet
Author: User

V. Optimizing SQL statements


The previous chapter tells you how to design a good table, and a good table naturally requires a good SQL statement to manipulate it. This chapter will talk about how to optimize SQL statements.


1. SQL Statement Optimization principles

Optimize the query that needs to be optimized

Positioning Optimization Object Performance bottlenecks

Start with the explain

Complete sorting in the index whenever possible

Take only the column you need

Avoiding complex joins and subqueries as much as possible

2. Optimize limit

SELECT * from Test1 ORDER by ID limit 99999,10

The original statement uses the ID index, but the equivalent of locating from the first row to 99999 rows and then to scan the 10 rows, the equivalent of scanning the entire table

If you change to
SELECT * from Test1 where id>=100000 order by ID limit 10

Then navigate directly to the 100000 lookup


3. Try to avoid the SELECT * command


4. Do not allow sorting

When a group by group query is used, the default grouping is also sorted and may slow down .

Like what:


in the Group by back Add ORDER BY null prevents sorting.


5. Use a connection to replace a subquery

In some cases, you can use a connection to replace a subquery. Because using Join,mysql, you do not need to create temporary tables in memory.

[Poor efficiency]

SELECT * from Sales2 where company_id not in (select ID from Company2)

[Simple handling method]


[Left outer connection, highest efficiency]


SELECT * FROM dept LEFT join EMP on DEPT.DEPTNO=EMP.DEPTNO;  

6. Use limit 1 to obtain a unique row

Sometimes, when you want to query a table, you know you just need to look at one line. In this case, adding a limit of 1 will make your query more efficient. This allows the database engine to discover that only 1 will stop scanning, instead of scanning the entire table or index

7. Use exists instead of in

exists is far more efficient than in. It is related to full table scan and range scan. Almost overwrites all in-action clause queries to subqueries that use exists

8. Don't be a cheap hand

Do not use distinct and order by when it is not necessary.

These actions can be performed on the client side, which adds additional overhead



At the end of this chapter, the next chapter discusses other ways to optimize MySQL databases.

Database Tuning Tutorial (12) Optimizing SQL statements

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.