Indexes - More

[jimguy999]

A second question on indexes.

If I have a multi table join - do I put indexes on each of the fields.

For example

TableA
Col1
Col2
Col3
Col4
Col5
Col6

TableB
Col1

TableC
Col3

TableD
Col4

TableE
Col5

So the join looks like this

Select ....
From TableA a inner join TableB b on
a.Col1 = b.Col1 inner join TableC c on
a.Col3 = c.Col3 inner join TableD d on
a.Col4 = d.Col4 inner join TableE e on
a.Col5 = e.Col5

So, Each table joins on a different colunm in TableA. These values are character values.

So, is it best to have an index which covers all the fields used in the join in TableA (i.e. Col1, Col3, Col4, Col5), or is it best to have 4 separate indexes (my gut feeling says no), or not have any index at all. This table will probably have > 300,000 records in it. In the real case, the main table (in this example, TableA) is joined with 7 other tables.

fyi - TableB, TableC, TableD, and TableE are small tables (i.e. less than 100 rows)

Any thoughts?

Thanks in advance

[skhanal]

You can have one composite index with all columns for TableA, but you also need to create indexes on B,C,D and E.