Newbie question about indexes

Hi ,

I have following statement :

SELECT * FROM Table WHERE Col1=@.Var1 AND Col2=@.Var2 ... AND ColN=@.VarN

How should I design indexes for best performance ?
(
Add one index on columns Col1 till ColN
or add N indexes, first for column Col1, second for Col2, ...
)

Thanks, for your suggestions

How many rows do you expect it to return?
Do you prefer retrieval speed over update speed?
Do you have other queries that might benefit from individual indexes?

|||Strictly from your query perspective, you will only need one index with the key (col1...colN). However, this index will be useless if say col1 is missing from your where clause. So you need to evaluate full set of queries that you plan to run on this table. Also, as Eric points out, you will need to evaluate the cost of updating indexes if you have lots of updates/inserts/deletes
Thanks|||

Hi,

I expect to return c. 100 rows. (Would indexing strategy differs if I have return whole table ?)
Retrieval speed is priority.
I have no other queries for this table.

Thank you

|||Here's what the optimizer guys have to say:

One index should be fine if he always has every column in the WHERE condition and he is doing equality matching.

This will give two main plan options:

1. index lookup + fetch
2. table scan

It will be a cost-based decision.