0

I have three tables in my database:

users:

  • user_id (PK)

roles:

  • role_id (PK)

users_roles:

  • user_id (PK, FK)
  • role_id (PK, FK)

I've done plenty of research surrounding indexing columns but I can't find a definitive answer to my question which is what columns should be indexed in this bridging scenario when I have a compound primary key. I need to be able to conduct WHERE queries using both user_id and role_id in the bridging table. I've read mixed answers and don't understand how many indexes need to be defined.

My current indexes for this bridging table include:

  • PRIMARY (user_id, role_id)
  • INDEX (user_id)
  • INDEX (role_id)

I assume ^ is incorrect.

1
1

By default PRIMARY KEY is already a clustered indexed. It will also speed up queries executed against first column in an index, but not the other.

So if you index is defined on user_id, role_id (in that order), index will work also for user_id.

In your situation I'd go with default index of compound key, then, if needed, take a closer look at long running queries and benchmark if separate index on role_id is necessary.

3
  • Ok, so in my case the INDEX user_id should be removed because the PRIMARY cluster covers it? – Owen Apr 12 '20 at 6:44
  • @Owen No, it won't be removed, but it's unnecessary. But you must keep in mind that every index requires some additional processing when inserting or deleteing records, so you don't want to have many indexes, which are not necessary. And, as I said, everything depends. If you encounter some long running queries, you might benchmark if additional indexes help, but for start, you need only additional index on role_id, but only if you have queries that may benefit from that index, don't create unnecessary indexes. – Michał Turczyn Apr 12 '20 at 6:49
  • 1
    @Michal, I think he is asking whether he should remove the index. The answer is yes which I beleive we both agree on – Lennart Apr 12 '20 at 8:38

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.