Brief Introduction to database query optimization principles www.2cto.com step method 1. To optimize queries, try to avoid full table scanning. First, consider creating an index on the columns involved in where and orderby. 2. Try to avoid null value determination on the field in the where clause. Otherwise, the engine will discard the index and perform a full table scan, such as www.
Brief Introduction to database query optimization principles www.2cto.com STEP/method 1. To optimize queries, avoid full table scan as much as possible. 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 a full table scan, such as www.
Introduction
Database Query Optimization Principle www.2cto.com
STEP/Method
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 a full table scan, such as www.2cto.com.
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.in and not in should also be used with caution, otherwise it will cause a full table scan, such:
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 wherenum = @ num
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 whenever possible, which 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. do not perform function operations on fields in the where clause as much as possible. This will cause the engine to stop using indexes 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
