When adding a composite index, Equal Calculation fields should be placed at the beginning

 

There is a common misunderstanding that we should put the highly selective field at the top. This is usually only for the index of a field, for the composite index, we often need to put the field for Equality calculation at the beginning to look at the test.

Use adventureworksgocreate table demo1 (ID int identity (1000) primary key, Gender char (1) not null, age int not null, description varchar () default (replicate ('A ', 1000) -- fill the Number Auxiliary table -- drop table # numselect row_number () over (order by customerid) as number into # num from adventureworks. sales. individual -- insert test data insert demo1 (gender, age) Select case when Number % 2 = 0 then 'F' else 'M' end, ABS (checksum (newid ())) % 80 from # num

 

Create the following indexes:

CREATE INDEX ix_age_sex ON demo1(age,gender) include(description) WITH (online=on)

Query the following statements:

DBCC FREEPROCCACHECHECKPOINTDBCC DROPCLEANBUFFERSSELECT age,gender,description FROM demo1  WHERE gender='f' AND age BETWEEN 30 AND 40

Logic reading:

(1236 row (s) affected)
Table 'demo1'. Scan count 1,Logical reads 350, Physical reads 3, read-ahead reads 346, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Delete the original index and create the index with low selectivity

DROP INDEX ix_age_sex ON demo1 CREATE INDEX ix_age_sex ON demo1(gender,age)INCLUDE (description) WITH (online=on)

To query the same statement:

DBCC FREEPROCCACHECHECKPOINTDBCC DROPCLEANBUFFERSSELECT age,gender,description FROM demo1  WHERE gender='f' AND age BETWEEN 30 AND 40

Logic reading:

(1236 row (s) affected)
Table 'demo1'. Scan count 1,Logical reads 181, Physical reads 3, read-ahead reads 178, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

We can see that the number of low-selective fields in the front of the logical read is reduced by 350 to 181, resulting in performance improvement.

Conclusion: 1: The field used for Equality calculation is placed at the beginning. If there are multiple equality operations, the selectivity of equality calculation fields must be considered.