Using non clustered index for efficient searching

Question

I have created non clustered index on combination of three columns. Do I need to specify all the three columns in where condition to used non clustered index.

I don't think so. But in order for that index to be used, the first column must be in the WHERE clause. — Felix Pamittan
– Felix Pamittan, Commented Sep 11, 2015 at 6:30

StackUser · Accepted Answer · 2015-09-11 06:38:05Z

1

In order to utilize your index you have to use like this.

Index: Ix_threecols(c1,c2,c3)

1. select c1,c2,c3 from table where c1=val
and c2=val and c3=val
2. select c1,c2,c3 from table where c1=val
and c2=val
3. select c1,c2,c3 from table where c1=val

Your left most first column in the index should be used in the where clause. Please don't use (*) in your select since it may lead to index scan.

We have to check the execution plan for more analysis on this.

answered Sep 11, 2015 at 6:38

StackUser

5,3983 gold badges29 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

gkarya42 Over a year ago

what if i use where c1=val or c2 =val

StackUser Over a year ago

You can use like this but it would be an index scan if you use 'or'

Mureinik · Accepted Answer · 2015-09-11 06:37:46Z

1

The short answer is no.

If at least the first column in the index appears in your where clause, the index can be used. Whether or not it will be used is already up to the optimizer, but assuming you don't have any other indexes on other columns in the where clause, it will probably be used.

answered Sep 11, 2015 at 6:37

Mureinik

316k54 gold badges399 silver badges405 bronze badges

Collectives™ on Stack Overflow

Using non clustered index for efficient searching

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related