CODEWITHSUNDEEP
sql-servert-sql
Olivier De Meulder
Olivier De Meulder

Reputation: 2501

Indexes for where clause and join clause

Consider the following:

And this is my query:

select t2.a, t2.b, t1.m
from table2 t2
join table1 t1 on t1.a = t2.a 
              and t2.b = t2.b
where t2.x = 'some value'
  and t2.y = 'some other value'

I have to optimize this query.

I have the following non-clustered indices:

Would I benefit from another index on table2 that would cover all the columns used in this query: a, b, x and y?

Upvotes: 1

Views: 2014

Answers (1)

Razvan Socol
Razvan Socol

Reputation: 5684

Considering that the x and y combination will provide a single row, it's important to have an index on table2 on x and y, as well as an index on table1 on a and b. Optionally, you could make the first index unique and add included columns, like this:

CREATE UNIQUE INDEX IX_table2_x_y ON table2 (x,y) INCLUDE (a,b)
CREATE INDEX IX_table1_a_b ON table1 (a,b) INCLUDE (m)

Upvotes: 1

Related Questions