Jump to content

Which is better for performance


MrAdam
 Share

Recommended Posts

Hi guys,I was just curious as to which you thought would be the most efficient method of storing friendships.a) One row per relationship. Draw-backs will obviously be when you have to check against 2 columns to retrieve a list of a particular user's friends, or just checking if a certain user is in their friend's list. The returned result set would also be harder to work with. Or,:) Two rows per friendship. Main draw-back being that twice the memory is needed, and inserting/updating would be a little more complex, however the queries would be much more optimized, and the returned data would be consistent.I'm more included towards b, but what would you say?Thanks for any responses,Adam

Link to comment
Share on other sites

How about table friendships where you haveID_friendship (auto increment)ID_userID_friendand use query: SELECT ID_friend FROM friendships WHERE ID_user='$usersid'So there could be duplicated items. Example:I'm 1, you are 2So:ID_friendship --- ID_user --- ID_friend // Query on this will result my friends------ 1 ------ ------- 1---- ------- 2 -----And also it must be:ID_friendship --- ID_user --- ID_friend // Query on this will result your friends------ 2 ------ ------- 2---- ------- 1 -----

Link to comment
Share on other sites

I'm not sure I see what you're talking about with this two rows per relationship model... care to give an example?If you make both ID_user and ID_friend a primary (non auto incrementing) key, you don't need ID_frindship... that would be the best space wise and integrity wise approach.

Link to comment
Share on other sites

Okay let's say the table is structured roughly as you say, boen_robot, like:

create table friendships (	user_id mediumint unsigned not null,	friend_id mediumint unsigned not null,	primary key (user_id, friend_id));

And we have the following data:

insert into friendships values (1, 2), (3, 1), (2, 3);

So 1 is friends with 2 and 3, 2 is friends with 3 (and vice-versa obviously, given that the friendship would be a two way thing). We store the user who requested the friendship in `user_id`, and the friend who accepted in `friend_id`.So as an example, let's say I wanted to return all the friends of 1, I'd have to check both columns:

select * from friendships where user_id = 1 or friend_id = 1;

That would give me:+---------+-----------+| user_id | friend_id |+---------+-----------+| 1 | 2 || 3 | 1 |+---------+-----------+It wouldn't be difficult based on the ID we queried with to arrange these correctly, but that's adding complexity to select queries, as well as overhead on the database to check both columns.Instead if we inserted two rows for each friendship:

truncate friendships;insert into friendships values (1, 2), (2, 1), (1, 3), (3, 1), (2, 3), (3, 2);

We could then query with:

select * from friendships where user_id = 1;

To produce:+---------+-----------+| user_id | friend_id |+---------+-----------+| 1 | 2 || 1 | 3 |+---------+-----------+Which would mean cleaner, faster select statements, but obviously twice the memory.What I'm unsure about is if my logic is right; or would the memory issue out-weigh the cleaner/simpler statements?

Edited by MrAdam
Link to comment
Share on other sites

A friendship is not necesarily a mutual and transit act... you may have me as someone who you think of as a friend, but should I have you as mine if I don't know you myself (but a friend of mine knows you) or don't want you as my friend anymore for whatever reason even though you still want us to be friends?The first kind of table shows you not only who is friends with who, but in which user's list the friend is. As Haris_S put it, you can do

SELECT friend_id FROM friendships WHERE user_id=$usersid

and get all friends in a certain user's list... not necesarily mutual friends. And you can use something like:

SELECT f.friend_id FROM friendships AS f WHERE f.user_id=$usersid AND $usersid IN (SELECT mf.friend_id FROM friendships AS mf WHERE mf.user_id = f.friend_id)

to get only mutual friends.Yes, the query for mutual friends is more complex, but the processing overhead (in the above query at least; there may be a more efficient query for the same thing) is only going to be big if all friends are mutual.

Link to comment
Share on other sites

Well, the same queries would work for the double row model too, only this time you can assume all friends are mutual if you care to add both combinations at insert time, and therefore improve perfroamance by using the first query... at the price of using double the space as you point out.With a single row model... I think you may be able to do something like

SELECT user_id WHERE friend_id = $useridUNIONSELECT friend_id WHERE user_id = $userid

to get all (allegedly mutual) friends of a user, all conviniently palced in a single column in the result set. The statement is a little more complex, but the overhead is probably less than that of the previous more complex query (a UNION basically appends one set to the other, removing items that duplicate with ones from the first along the way)

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...