Query Optimization Principles

1. To optimize the query, try to avoid full table scanning. First, consider creating an index on the columns involved in where and order.

2. Try to avoid null value determination on the field in the WHERE clause. Otherwise, the engine will discard the index and perform full table scanning, for example:
Select ID from t where num is null
You can set the default value 0 on num to make sure that the num column in the table does not have a null value, and then query it like this:
Select ID from t where num = 0

3. Try to avoid using it in the WHERE clause! = Or <> operator. Otherwise, the engine will discard the index for full table scanning.

4. Try to avoid using or in the WHERE clause to connect to the condition. Otherwise, the engine will discard the index and perform full table scanning, for example:
Select ID from t where num = 10 or num = 20
You can query it as follows:
Select ID from t where num = 10
Union all
Select ID from t where num = 20

5. Use in and not in with caution. Otherwise, a full table scan may occur, for example:
Select ID from t where num in (1, 2, 3)
For continuous values, you can use between instead of in:
Select ID from t where num between 1 and 3

6. The following query will also cause a full table scan:
Select ID from t where name like '% ABC %'
To improve efficiency, you can consider full-text search.

7. If a parameter is used in the WHERE clause, a full table scan is performed. Because SQL parses local variables only at runtime, the optimizer cannot postpone the selection of the access plan to runtime; it must be selected at compilation. However, if an access plan is created during compilation, the value of the variable is still unknown and thus cannot be used as an input for index selection. The following statement performs a full table scan:
Select ID from T [email protected]
You can change it to force query to use the index:
Select ID from T with (index name) wherenum = @ num

8. Avoid performing expression operations on fields in the WHERE clause as much as possible. This will cause the engine to discard the use of indexes for full table scanning. For example:
Select ID from t where num/2 = 100
Should be changed:
Select ID from t where num = 100*2

9. Avoid performing function operations on fields in the WHERE clause as much as possible, which will cause the engine to stop using the index for full table scanning. For example:
Select ID from t where substring (name, 1, 3) = 'abc' -- id whose name starts with ABC
Select ID from t where datediff (day, createdate, '2017-11-30 ') = 0 -- '2017-11-30' generated ID

10 when using a composite index, no leading column will cause a full table scan.

Query Optimization Principles