SQL Optimization Policy advanced optimization often uses -1 (the Return of the King)

1 frequently Used optimization strategies1.1 Statements1.1.1 Using the actual column name

When we query the SQL statement. Do you feel that using actual column names is faster than using * *? The answer is yes.

To prove this, interested friends can verify for themselves. I give an example here.

SELECT * FROM user;--time: 0.423ms</span>

Select  id,version,avatar,date_created,description,email,email_show,enabled,first_name,last_name,last_ UPDATED,MEMBER_ID,PASSWD,USER_REAL_NAME,USER_STYLE_ID, Username,paypal_email_address,transaction_fee_payer,pay_ Pal_email_addressfrom user;--Time: 0.177ms

1.1.2 Rational Use of having

To understand the use meaning of having, it is used only to filter the number of rows to which all queries are made. That It's a filter.

Do not use the having for any other purpose.

SELECT subject, count (subject) from Student_details WHERE subject! = ' science ' and subject! = ' Maths ' GROUP by subject

Instead of using the following unreasonable methods:

SELECT subject, count (subject) from Student_details GROUP by subject have subject!= ' Vancouver ' and subject!= ' Toronto '

1.1.3 Reducing the number of subqueries

Reducing the number of subqueries can reduce unnecessary performance consumption. Take a look at the following simple examples here.

Select name from the employee WHERE (salary, age) = (select Max (Salary), MAX (age) from employee_details) and dept = ' Electr Onics '

Replace the following statement

Select name from Employeewhere salary = (select Max (Salary) from employee_details) and age = (select Max (age) from Employe e_details) and emp_dept = ' Electronics '

Frequently used optimization tips:

1. Reasonable use of exits, in, Joinkeyword.

2. In generally reduces the efficiency of SQL, when filtering criteria in a word query, the use of in will improve the efficiency of operation, when most of the filtering standards in the query. Exits operating efficiency is relatively high.

For example, the following sample:

Select *from Product P where EXISTS (select 1 from Order_items o where o.product_id = p.product_id)

Replace the following SQL Statement

SELECT * from Product p where product_id in (select product_id from Order_items)

3 Replace Distinct with exits.

When there is a one-to-many relationship between tables, using exits is more efficient than using distinct.

selectd.dept_id, d.dept from Dept D WHERE EXISTS (SELECT ' X ' from employee e wheree.dept = d.dept)

Replace the following SQL Statement

SELECT DISTINCT d.dept_id, d.dept from dept d,employee e WHERE e.dept = e.dept

4 Differences between UNION ALL and union

5 optimize where query conditions.

Look at the following example, the comparison. You will find the difference in performance.

Selectid,email,first_name,last_name from user where first_name like ' sha% '--time: 0.032ms</span>

<span style= "FONT-SIZE:18PX;" >selectid, email, first_name, last_name from user where substr (first_name, 1, 3) = ' Sha '--time: 0.035ms

Example 2

SELECT product_id, product_name from product WHERE unit_price between MAX (Unit_price) and MIN (Unit_price)

Replace the following SQL statement

SELECT product_id, product_name from product WHERE unit_price >= MAX (unit_price) and Unit_price <= MIN (unit_price)

1.1.4 using DECODE

Use the Decode function to avoid scanning the same rows or connecting the same tables over and over again. Decode are often used in group by SQL statements.

SELECT Idfrom employee WHERE namelike ' ramesh% ' andlocation = ' Bangalore '

Instead of the following SQL statement

SELECT DECODE (location, ' Bangalore ', id,null) ID fromemployee WHERE name is like ' ramesh% '

Copyright notice: This article Bo Master original articles, blogs, without consent may not be reproduced.

SQL Optimization Policy advanced optimization often uses -1 (the Return of the King)