Common optimization techniques for SQL statements (i)

The most common way to improve the efficiency of SQL statements is to build indexes and avoid full table scans as much as possible. Let's tidy up some common SQL optimization techniques to avoid full table scanning. A simple optimization may be able to make your SQL execution efficiency several times, even dozens of times times.

1. Avoid using is null or is not NULL in the WHERE clause to judge a field.

Such as:

Select ID from table where name is null

In this query, even if we set an index for the name field, the query parser is not used, so the query is efficient. To avoid such queries, when the database is designed, try to set the default values for fields that may appear null values, if we set the default value of the Name field to 0, then we can query:

Select ID from table where name = 0

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

Such as:

Select name from table where ID <> 0

When the database is queried, the index is not used for the! = or <> operator, and the database is used for <, <=, =, >, >=, between, and. So for the above query, the correct wording should be:

Select name from table where ID < 0 UNION ALL select name from table where ID > 0

Why do we not use or to link the two conditions after the where? That's the 3rd optimization trick we're going to talk about.

3. Avoid using or in the WHERE clause to link conditions.

Such as:

Select ID from table where name = ' C + + ' or name = ' C # '

In this case, we can write:

Select ID from table where name = ' C + + ' UNION ALL select ID from table where name = ' C # '

4, less in or not in

Although the index is used for in conditions, there is no full table scan, but in some specific cases, using other methods may be better. Such as:

Select name from table where ID in (1,2,3,4,5)

Like this continuous number, we can use between and, for example:

Select name from table where ID between 1 and 5

5. Note the use of wildcard characters in like.

The following statement causes a full table scan to be used sparingly. Such as:

Select ID from table where name is like '%jayzai% '

Or

Select ID from table where name is like '%jayzai '

The following statement performs much faster because it uses the index:

Select ID from table where name is like ' jayzai% '

6. Avoid expression operations on fields in the WHERE clause.

Such as:

Select name from table where ID/2 = 100

The correct wording should be:

Select name from table where id = 100*2

7. Avoid function operations on fields in the WHERE clause.
Such as:

Select ID from table where substring (name,1,8) = ' Jayzai '

Or

Select ID from table where DATEDIFF (Day,datefield, ' 2014-07-17 ') >= 0

The fields are function-treated in these two statements, so that the query parser discards the use of the index. The correct wording is this:

Select ID from table where name is like ' jayzai% '

Or

Select ID from table where Datefield <= ' 2014-07-17 '

In other words, do not perform functions, arithmetic operations, or other expression operations on the left side of the = on the WHERE clause.

8, in sub-query, with exists instead of in is a good choice.

Such as:

If we change this statement to the following wording:

In this way, the query comes out the same, but the following statement is much faster to query.

Common optimization techniques for SQL statements (i)