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.

Improve this question
1

1 Answer 1

Reset to default
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.

Improve this answer
3
  • Ok, so in my case the INDEX user_id should be removed because the PRIMARY cluster covers it?
    – Owen
    Apr 12, 2020 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, 2020 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 - Slava Ukraini
    Apr 12, 2020 at 8:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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