7 Common SQL Optimization tips

As programmers often interact with the database very frequently, it is necessary to master some of the optimization techniques of SQL. Here are some common SQL optimization tips, interested friends can understand.

1. Note the use of like in a wildcard

The following notation causes a full table scan, for example:

Select  from where  like ' %name% ' or Select  from where  like ' %name '

The following notation is much more efficient because it uses an index

Select Id,name from userinfo where name like ' name% '

2. Avoid functional manipulation of fields in the WHERE clause

Like what:

 select  ID from  userinfo where  substring  (name, 1 , 6 ) =   "   "  or  select  ID from  userinfo where  datediff  (day , Datefield,   2017-05-17   ) >=  0  

Both of the above sentences function with the field, causing the query parser to discard the use of the index.

The correct wording:

Select  from where  like ' xiaomi% ' Select  from where <= ' 2017-05-17 '

The popular understanding is that the WHERE clause ' = ' does not appear on the left side of functions, arithmetic operations, or other expression operations

3, in sub-query, try to use exists instead of in

Select  from where inch (Select from userinfo b) can be changed to Select  from where exists (Select1fromwhere= a.id)

The following queries are much faster than in queries.

4. In the WHERE clause try not to use is null or is not NULL to judge the field

For example:

Select  from where  is NULL

Try to avoid null in the database field, and if the condition is null for the query, the index will not be used, resulting in low query efficiency.

Therefore, when the database is designed, when possible to set a field may be empty when the default value, then the query can be

Queries are based on default values, such as the Name field is set to 0, and the query statement can be modified to

Select  from where name=0

5. Avoid using or as a link condition in the WHERE clause

For example:

SelectId fromUserInfowhereName='xiaoming' orName='Xiaowang'can be rewritten as:SelectId fromUserInfowhereName= 'xiaoming' Union  AllSelectId fromUserInfowhereName= 'Xiaowang'

6. Avoid using the! = or <> operator in the WHERE clause.

For example:

Select  from where <> 0

Description: When the database is queried, the index is not used for! = or <> operators.

For <, <=, =, >, >=, between and, the database will use the index.

Therefore, for the above query, the correct wording can be changed to:

Select  from where < 0 Union  All Select  from where > 0

7, less in or not in

For continuous numeric range queries, use between and as much as possible, for example:

Select  from where between  Ten  and  -

These are only relatively common SQL optimization techniques, of course, there are many welcome to add!

Welcome to the public number: Donet Technology sharing platform

7 Common SQL Optimization tips