index on joined columns

I have and joined between two tables like this:
......TB1 join TB2 on TB1.col1=TB2.col3 and TB1 .clo2=TB2.Col4
There a lot of records in TB2 ,so I wondered to make an index on Col3 and
Col4 of TB2.Now my question is that according to the join above it's better
to have a compound index on col3,col4 or each inividually?
Thanks for your help.
RayHi Ray,
I recommend you to create a composite Non clustered index on Col1 and Col2
of Table 1 , Col3 and Col4 of Table 2.
This index will be very good if you have a clustered index on Key column.
You can also use the Index tuning Wizard for Index recommendations. As well
see the Logical and Physical reads, Cpu usage before and
after the creation of index.
Thanks
Hari
SQL Server MVP
"RayAll" <RayAll@.microsft.com> wrote in message
news:%238E3mJjOFHA.3296@.TK2MSFTNGP15.phx.gbl...
>I have and joined between two tables like this:
> ......TB1 join TB2 on TB1.col1=TB2.col3 and TB1 .clo2=TB2.Col4
>
> There a lot of records in TB2 ,so I wondered to make an index on Col3 and
> Col4 of TB2.Now my question is that according to the join above it's
> better to have a compound index on col3,col4 or each inividually?
> Thanks for your help.
> Ray
>

Index behaviour.

Hi all,

I have a non clustered index IDX_Col1234 on Col1,Col2,Col3,col4.

Which of the following Query would benefit from the above mentioned index ?

Query 1:
=======
SELECT Col1, Col2 , Col3, Col4, Col5 FROM TABLE
WHERE Col5 = @.Val

Query 2:
=======

SELECT Col1, Col2 , Col3, Col4, Col5 FROM TABLE
WHERE Col1 = @.val1
AND Col2 = @.val2
AND Col3 = @.val3
AND Col4 = @.val4

Query 3:
=======

SELECT Col1, Col2 , Col3, Col4, Col5 FROM TABLE
WHERE Col1 = @.val1
AND Col2 = @.val2

Query 4:
=======

SELECT Col1, Col2 , Col3, Col4, Col5 FROM TABLE
WHERE Col3 = @.val3
AND Col4 = @.val4

Query 5:
=======

SELECT Col1, Col2 , Col3, Col4, Col5 FROM TABLE
WHERE Col1 = @.val1
AND Col4 = @.val4

Query 6:
=======

SELECT Col1, Col2 , Col3, Col4, Col5 FROM TABLE
WHERE Col4 = @.val4
AND Col2 = @.val2

Thanks in advance,

DBAnalyst

Hi,

Here is a short summary of the use of the index:

QUERY 1:
no index is used because col5 is not added to the index

QUERY 2:
index will be used and then a lookup will be done to search for col5 which is not included in the index

QUERY 3:
index will be used and then a lookup will be done to search for col5 which is not included in the index

QUERY 4:
Index will be used and then a lookup will be done to search for col5 which is not included in the index
Here the index is less performant because the columns that are referenced, are not in front of the list of columns in the index.

QUERY 5:
Index will be used and then a lookup will be done to search for col5 which is not included in the index.
Here the index gives better results compared to QUERY 4 because Col1 is included as first column

QUERY 6:
Same as QUERY 4

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||Geert's comments are right, provided there are no other indexes on these columns. You could have multiple indexes on the table - if you have one with only col1 and col2 this might get used instead of the one you mentioned. SQL tries to match the index that has the most columns in the WHERE condition. If the columns in your SELECT list are from the Index, then its faster, if the columns are not in any of the indexes, it has to do lookup for those columns.|||Also you can check the same by applying the index hint on your SELECT statment then check the estimated execution plan.|||

I would like to add my two cents here, an existing index will qualify to be use in the execution plan only when the order of the columns in the where clause follow the same order as the index structure. Otherwise is doing an index scan and an index scan is not really improving the performance.

If you still have questions, I invite you to create the table dump test data and compare the execution plans with your own eyes.

So on my opinion queries 4 and 6 don't match the index structure that's why they scan the index.

Index behaviour.

Hi all,

I have a non clustered index IDX_Col1234 on Col1,Col2,Col3,col4.

Which of the following Query would benefit from the above mentioned index ?

Query 1:
=======
SELECT Col1, Col2 , Col3, Col4, Col5 FROM TABLE
WHERE Col5 = @.Val

Query 2:
=======

SELECT Col1, Col2 , Col3, Col4, Col5 FROM TABLE
WHERE Col1 = @.val1
AND Col2 = @.val2
AND Col3 = @.val3
AND Col4 = @.val4

Query 3:
=======

SELECT Col1, Col2 , Col3, Col4, Col5 FROM TABLE
WHERE Col1 = @.val1
AND Col2 = @.val2

Query 4:
=======

SELECT Col1, Col2 , Col3, Col4, Col5 FROM TABLE
WHERE Col3 = @.val3
AND Col4 = @.val4

Query 5:
=======

SELECT Col1, Col2 , Col3, Col4, Col5 FROM TABLE
WHERE Col1 = @.val1
AND Col4 = @.val4

Query 6:
=======

SELECT Col1, Col2 , Col3, Col4, Col5 FROM TABLE
WHERE Col4 = @.val4
AND Col2 = @.val2

Thanks in advance,

DBAnalyst

Hi,

Here is a short summary of the use of the index:

QUERY 1:
no index is used because col5 is not added to the index

QUERY 2:
index will be used and then a lookup will be done to search for col5 which is not included in the index

QUERY 3:
index will be used and then a lookup will be done to search for col5 which is not included in the index

QUERY 4:
Index will be used and then a lookup will be done to search for col5 which is not included in the index
Here the index is less performant because the columns that are referenced, are not in front of the list of columns in the index.

QUERY 5:
Index will be used and then a lookup will be done to search for col5 which is not included in the index.
Here the index gives better results compared to QUERY 4 because Col1 is included as first column

QUERY 6:
Same as QUERY 4

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||Geert's comments are right, provided there are no other indexes on these columns. You could have multiple indexes on the table - if you have one with only col1 and col2 this might get used instead of the one you mentioned. SQL tries to match the index that has the most columns in the WHERE condition. If the columns in your SELECT list are from the Index, then its faster, if the columns are not in any of the indexes, it has to do lookup for those columns.|||Also you can check the same by applying the index hint on your SELECT statment then check the estimated execution plan.|||

I would like to add my two cents here, an existing index will qualify to be use in the execution plan only when the order of the columns in the where clause follow the same order as the index structure. Otherwise is doing an index scan and an index scan is not really improving the performance.

If you still have questions, I invite you to create the table dump test data and compare the execution plans with your own eyes.

So on my opinion queries 4 and 6 don't match the index structure that's why they scan the index.