I need an expert mysql opinion for doing a friends system

Question

I need some help designing a friends system

The mysql table:
friends_ list
- auto_ id
- friend_ id
- user_ id
- approved_ status

Option 1 = Everytime a user adds a user there is 2 entries added to the DB, we then can get there friends like this

SELECT user_id FROM `friends_list` WHERE friend_id='$userId' and approved_status='yes'

---

Option 2 = We add 1 entry for every friend added and then get the friend list like this

SELECT friend_id AS FriendId FROM `friends_list` WHERE user_id='$userId' and approved_status='yes'
UNION
SELECT user_id as FriendId FROM `friends_list` WHERE friend_id='$userId' and approved_status='yes'

Of the 2 methods above for having friends on a site like myspace, facebook, all the other sites do, which of the 2 methods above would be best performance?

The 1st method doubles the ammount of rows, example a site with 2 million friend rows would only be 1 million rows the second method.

However does the union method mean there is 2 queries being made, so 2 queries on a million row table instead of 1?

---

UPDATE

I just ran some test on 60,000 friends and here are the results, and yes the tables are indexed.

Option 1 with 2 entries per friend;
.0007 seconds

Option 2 with 1 entry per friend using UNION to select
.3100 seconds

Answer 1

Option 1.
Do as much work as possible on adding a friend. Adding someone is very rare compare to selecting all friends. Something probably done every time you render a page (on a social site).

Answer 2

If you are indexing on user_id and friend_id, then the two statements should be equivalent in time -- best to test though -- DB's can have surprising results. The UNION is seen by the database and it can use it to optimize the query, though.

Is friendship always mutual and with the same approval status? If so, I'd opt for the second one. If it can be one-way or with separate approvals, then you need the first one, right?

Answer 3

Answers to this kind of question tend to depend upong the usage patterns. In your case which is most common: adding new friendship relationships or querying the friendship realtionship?

Also you need to consider future flecibility, what otehr uses might there be for the data.

My guess is that option is a cleaner data model for what you are trying to represent. It looks like it allows for expandsion in directions such as asymmetric relationships, and friend of friends. I think that option will prove to be unweildy in the future.

Answer 4

Which dbms are you using? MySQL always use an extra temporary table when executing union queries. Creating these temporary tables creates some overhead to the query and is probably why your union query is slower than the first query.

Have you thought about separating friends and friend request? This would reduce the content of the friends table, you would also be able to delete accepted requests from the friend request table and keep the size of the table down. Another good advantage is that you keep less columns in each table and it is thereby easier to get a fine tuned index on them.

I am currently building this feature myself, and it would be interesting to hear about your experience on the matter.