I have a composite table called TeacherClass with two columns, TeacherID and ClassID. The primary key is both the TeacherID and ClassID. Even though these are also considered foreign keys, should I add an index to each column separately even though they have one on both of them combined from the primary key?
Add a comment
|
2 Answers
If you have a primary key on (TeacherID, ClassID), you already have an index on those two.
However, a query that uses only ClassID as its parameter won't be able to take advantage of that index - that index only works for (TeacherID) or (TeacherID, ClassID).
So yes - if you ClassID column is used as a foreign key into other tables, I would definitely argue you should put an index on just (ClassID).
An index on just TeacherID however is totally superfluous.
Comments
If they are both part of the PK, then most likely are already in a clustered index, but index will have, say (TeacherID, ClassID), not the other way around (ClassID, TeacherID). This means the table will be fast when running something like:
SELECT * FROM TeacherClass WHERE TeacherID = 9
But slow when running
SELECT * FROM TeacherClass WHERE ClassID = 9
If you are planning on running similar select statements, add a new index including ClassID and TeacherID, in that order. You won't need any separate indexes then (having an index that includes Col1 and Col2, makes an index that includes just Col1 redundant).
