What indexes do I need to define for my bridging tables? 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.
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.