Optimize Select Query [closed]

Question

I am using SQL 2000, and I am running a simple select statement on a table containing about 30 million rows. The select query looks like:

select col1, col2, col3 from Table1 where col4=@col4 and col5=@col5 and col6=@col6

The table has a clustered index in it (i.e. a primary key), but that is not being used as a where criteria. All the where criterias mentioned above have no indexed in them.

How can I optimize this query?

If I add indexes for each column in the where clause, would that make any difference?

If I have 10 columns in where clause, should all of those 10 columns have index in them?

Edit: This is probably one of the most common interview question :)

Answer 1

Yes, it will make a huge difference.

Instead of adding one index for each field, you should add one index that has the three fields. (How this is used in practice of course depends on how unique the fields are and what other queries you are going to use on the table.)

Note that adding an index also has a small negative impact when you insert or delete records into the table or update the indexed fields of a record.