5

I have a many-to-many join table between users and roles.

CREATE TABLE AppUserRole 
(
    UserId INT NOT NULL,
    RoleId INT NOT NULL, 

    CONSTRAINT PK_AppUserRole PRIMARY KEY (UserId, RoleId), 

    CONSTRAINT FK_AppUserRole_User FOREIGN KEY (UserId) 
        REFERENCES AppUser(UserId), 
    CONSTRAINT FK_AppUserRole_Role FOREIGN KEY (RoleId) 
        REFERENCES AppRole(RoleId)
);

CREATE INDEX IX_AppUserRole_RoleId ON AppUserRole(RoleId)
CREATE INDEX IX_AppUserRole_UserId ON User(UserId)

Since the compound key is clustered in the order of (UserId, RoleId), is the second index on UserId really necessary?

When you query for all rows for a specific UserId it's going to use the clustered index. Only when you query for all users in a specific RoleId will the index IX_AppUserRole_RoleId be used.

Is the index IX_AppUserRole_UserId necessary?

3

A query use the index in the column order (UserId first then RoleId). Without an index on RoleId, it will scan the clustered index. Unless there is a where clause with a userId, the engine does not know how to get inside the index whitout scanning all the userId.

Because roleId is a FK, best practice suggest to have an index on it. You definetly need it if you (often) query it.

Index on UserId alone is useless. The clustered index already does it since UserId is the entry point. Using it does not require an extra lookup to the PK in order to get data or associated roleId. Just clustered index seek.

  • 2
    The question is about IX_AppUserRole_UserId, not about IX_AppUserRole_RoleId. – Andriy M Aug 4 '15 at 8:47
  • I disagree that "Index on UserID alone is useless". There are definitely use cases for a narrow NC index that overlaps in part with a wider CI. The narrow NC index will be a LOT faster to do seeks on, especially if you aren't pulling data from the row beyond the userid. – JNK Aug 4 '15 at 13:09
  • In this case with only 2 columns, the index is useless. You will read User_id + PK from the index when you can read only PK from the cluster. If there were a lot more column and you only want to get role for a give user, in such case this index would be useful (read only user+pk instead of reading the whole row) – Julien Vavasseur Aug 4 '15 at 14:08
  • @JulienVavasseur thank you for the confirmation, in the case of this table, there is no other payload, so I will not create the additional index on UserId. – Richard Aug 4 '15 at 16:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.