-
Indexes - More
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
-
You can have one composite index with all columns for TableA, but you also need to create indexes on B,C,D and E.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|