Which processes faster; single or mulit-column indexes?

[Cyberfunkr]

I tried searching through the forums for this but nothing seemed to cover this exactly.

Given a vary simple database:

CREATE TABLE IF NOT EXISTS `Friend` (
  `user_id` int unsigned not NULL,
  `friend_id` int unsigned not NULL,
  `comment` varchar(250) default NULL)

Which would make more sense:
-- index (user_id) AND index (friend_id)
or
-- 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:
1,2,comment
and
2,1,comment

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.

/Cyberfunkr

[felgall]

If each of the two fields doesn't allow duplicates then specifying the second field in the index is redundant and provided that the database engine knows that there can be no duplicates the two versions should be treated identically. Of course since you also know it why not make sure of it and use the first version in the database so that it doesn't have to work out that you meant that when you entered the second version.

[Cyberfunkr]

If each of the two fields doesn't allow duplicates....

Well, there can be duplicates within each column. However no combination can be duplicated, but users can have multiple friends. So a fuller example would be:

1,2,comment
2,1,comment
1,3,comment
1,4,comment
2,4,comment
4,3,comment

etc.

There cannot be a
1,2,comment2

Does that clarify things?

Thanks