I'm developing a friendship relation in MySQL where friend relation is mutual. If A is friend of B, then B is friend of A. If one of the users end friendship then relation drops. I want to learn which way is better.
I have a running system;
user ----------- userid p.k name friends ------- userid friendid primary key (`userid`,`friendid`), key `friendid` (`friendid`) 1 2 2 5 1 3 To get all of my friends; SELECT u.name, f.friendid , IF(f.userid = $userid, f.friendid, f.userid) friendid FROM friends f inner join user u ON ( u.userid = IF(f.userid = $userid, f.friendid, f.userid)) WHERE ( f.userid = '$userid' or f.friendid = '$userid' )
This query works well. Maybe I can add a
UNION. The Query is more complicated than the one below and the table contains half as many records as the one below.
Another way is to keep relations in separate rows;
1 2 2 1 2 5 5 2 1 3 3 1 SELECT u.name, f.friendid FROM friends f inner join user u ON ( u.userid = f.friendid ) WHERE f.userid = '$userid'
This query is simple, although the table takes twice as much space.
My concern is; assuming that there are millions of users; which way will work faster?
What are the advantages and disadvantages of both ways?
What should I keep in mind or change for these ways? And what problems can I face for both ways?