How to setup indexes on a two-column table for quick querying on both columns?

Question

If I have a group members table with two columns in it: group_id and user_id, where users can be part of multiple groups and groups can contain many users, what would be the best way to setup the indexes? I want to be able to quickly determine which users are in a single group, so I think I would need to index on group_id, but I also want to quickly determine all the groups any single user is in, which makes me think I would also need to index on user_id.

Is it good to put a separate index on both group_id and user_id if those are the only columns in the table? Is there a better way to setup the indexes?

Answer 1

This sounds like something that should be a composite primary key, which would automatically index both columns and get you the best performance.