Creating an Index on a Foreign Key

[Brendan Vogt]

Hi, I have 3 tables, namely: CategoryGroup CategoriesInCategoryGroup Categorty CategoriesInCategoryGroup has only 2 fields, which I made the primary key each, namely CategoryGroupId and CategortyId, and they each reference the corresponding table. So basically they are foreign keys. I have a question when creating an index in the CategoriesInCategoryGroup table. Do I create 1 index, or should I create a separate index for CategoryGroupId and CategortyId in the CategoriesInCategoryGroup table? Please advise. Brendan

[DoomedOne]

Is always a good idea to create an index for each field (or group of fields) that forms a FK Only in few cases you could not create such an index, i.e. when it is the first field in an already existing index or is equal to de PK.

Habetis bona deum

[andyharman]

Hi Brendan I would expect CategoriesInCategoryGroup to have CategoryGroupId and CategortyId as a clustered primary key. You would then normally create a separate non-clustered index on the CategoryId column. If you are not using SQL-Server then the second index should be combine CategortyId and CategoryGroupId. Regards Andy