I tried searching through the forums for this but nothing seemed to cover this exactly.
Given a vary simple database:
Which would make more sense:
-- index (user_id) AND index (friend_id)
-- index (user_id, friend_id) AND index (friend_id, user_id)
In theory, each combination can happen only once in the database (with numbers reversed as a separate unique).
So there can be:
There are queries that look up a user_id and return all friends, and also looking up the friend and returning all the users that have "friended" them.
I have been using just single-column indexes, but was curious if going to multi-column would help anything.
Thanks for opinions and experiences.