0

I have read that all foreign keys should be indexed for better join performance. Do that mean, by definition, that all bridge tables should have all fields indexed

for example lets say i have 3 table

Project: Id, Name

ProjectApplication: Id, ProjectId, ApplicationId

Application: Id, Name

in these cases, should ProjectId and ApplicationId both have indexes on them?

leora
  • 188,729
  • 360
  • 878
  • 1,366
  • By that definition, the answer is obviously yes. But you don't have to follow that rule in all cases. – usr Apr 05 '14 at 23:52

1 Answers1

1

In your given example Id column in Project table have to be a Primary key(or atleast UNIQUE constraint) in order to be able to reference it in any other column i.e creating a foreign key constraint which references it same is true for Id column in Application table. So by default it will have a Clustered Index defined on it.

Now in your ProjectApplication table if you do create a foreign Key and create an Index on that column, and obviously when ever you need to retrieve information from these tables you will be joining these tables on these two fields so having a Clustered Index on one side and a nonclustered index on other side will most definitely have a great impact on the performance of your queries, well worth it , go for it .

M.Ali
  • 67,945
  • 13
  • 101
  • 127
  • thanks . .What do you mean by "Clustered Index on one side and a nonclustered index on the other side?" – leora Apr 05 '14 at 23:27
  • When you provide the join condition something like `ON A.ColumnPK = B.ColumnFK` , Primary Key column in Table A will have a cluster index on it by default. and if you create a nonclustered index on ColumnFK in table B. The query can really benefit from cluster index on columnPK in tableA and nonclustered index on ColumnFK of Table B. – M.Ali Apr 05 '14 at 23:36