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.