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.