Indexes: how many, how much?

[Mike H]

I have a number of tables whose queries include between one and three AND clauses with overlap in the columns being referenced.

For instance, the queries for one of the table might look like this:

select ColumnA
from TableA
where ColumnB = 5

select ColumnA
from TableA
where ColumnB = 5
and ColumnC = 2

select ColumnA
from TableA
where ColumnB = 5
and ColumnC = 2
and ColumnD = 7


In this example, how should I construct the indexes on TableA? Is it enough to have one index on each of Columns A, B and C? Or, should I create additional indexes that span multiple columns at a time?

Additionally, does the order of the columns matter at all? For instance, ColumnA might change with a greater frequency and have a broader range of values than ColumnB.

If there already exist indexes on the individual columns, should I keep them and leave them untouched?



Edited 1 time(s). Last edit at 05/07/2010 12:01AM by Mike H.