Seems like what you're looking for is a way to query your database more efficiently/quickly, and still find all links in either direction.

I think the use of a UNION statement should allow this. Basically write your query twice, joining on id1 the first time and id2 the second time, with "UNION" in the middle.

http://dev.mysql.com/doc/refman/5.0/en/union.html

I put together a quick setup like so:

****** start of SQL ******

create table people (
peopleid int unsigned not null auto_increment primary key,
name varchar(50) );

insert into people (name) values ('Dan');
insert into people (name) values ('Matt');
insert into people (name) values ('Jim');
insert into people (name) values ('Mark');
insert into people (name) values ('Chris');
insert into people (name) values ('Deanna');
insert into people (name) values ('Toni');
insert into people (name) values ('Teri');

create table friends (
id1 int not null,
id2 int not null,
key (id1),
key (id2) );

insert into friends (id1, id2) values (1, 2); /* Dan, Matt */
insert into friends (id1, id2) values (2, 1); /* Matt, Dan */
insert into friends (id1, id2) values (1, 4); /* Dan, Mark */
insert into friends (id1, id2) values (1, 5); /* Dan, Chris */
insert into friends (id1, id2) values (1, 6); /* Dan, Deanna */
insert into friends (id1, id2) values (1, 7); /* Dan, Toni */
insert into friends (id1, id2) values (8, 1); /* Teri, Dan */

/* This should mean that Dan (id 1) has designated friends of Matt, Mark, Chris, Deanna, and Toni, while Matt and Teri have both designated Dan as a friend.
Jim, if you're listening, sorry, nobody had said you're a friend.  ;)
What we should see from the query below is everyone who has a link in either direction with Dan (6 total) */

select p.name from friends f, people p
where p.peopleid = f.id1
and f.id2 = 1
UNION
select p.name from friends f, people p
where p.peopleid = f.id2
and f.id1 = 1;

+--------+
| name   |
+--------+
| Matt   |
| Teri   |
| Mark   |
| Chris  |
| Deanna |
| Toni   |
+--------+
6 rows in set (0.00 sec)

EXPLAIN select p.name from friends f, people p
    -> where p.peopleid = f.id1
    -> and f.id2 = 1
    -> UNION
    -> select p.name from friends f, people p
    -> where p.peopleid = f.id2
    -> and f.id1 = 1
    -> ;
+----+--------------+------------+--------+---------------+---------+---------+------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------+--------+---------------+---------+---------+------------+------+-------------+
| 1 | PRIMARY | f | ref | id1,id2 | id2 | 4 | const | 2 | | | 1 | PRIMARY | p | eq_ref | PRIMARY | PRIMARY | 4 | misc.f.id1 | 1 | Using where | | 2 | UNION | f | ref | id1,id2 | id1 | 4 | const | 4 | | | 2 | UNION | p | eq_ref | PRIMARY | PRIMARY | 4 | misc.f.id2 | 1 | Using where | |NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------+------------+--------+---------------+---------+---------+------------+------+-------------+
5 rows in set (0.00 sec)

****** END OF SQL ******

You can see from the results of the EXPLAIN query that MySQL (5.0.16 in my case) uses two primary key lookups, so appears to be operating pretty efficiently. I think even with a lot more data this is going to remain a very efficient query in MySQL.

MySQL before 4.0 did not support UNION.

Hope this helps!

Dan




"of course you have the problem where john has Joe as a friend but Joe
doesn't have john as a friend.  This seeming inconsistency, may or may not
be a problem depending on exactly what kind of a relationship you are trying
to define."

You've just hit the nail on the head! That's exactly the problem.

I think I might just have to grin and bear what I already have :-(

-----Original Message-----
From: 2wsxdr5 [mailto:[EMAIL PROTECTED]
Sent: 07 April 2006 15:11
To: Martin Gallagher; mysql@lists.mysql.com
Subject: Re: Social Network, linking members

Martin Gallagher wrote:

Hi,

I'm trying to find the most efficient way of "linking" members to one
another in a social networking application.

Currently I link them using 2 separate fields for the members: id1, id2.
So,
to find people in your network you would do:

I'm not sure exactly what it is you are doing but I think this may be
it.  You have a table of people and you want to know who is friends with
who.  I know 'friend' may not be the best term to use but it is easier
to type.  So I have my people table.

People{
  *PID,
  Name,
. . .
}

Then the Friend Table,

Friend{
 *PID,
 *FID
}

If you have person, John, with ID 234, and you want to know all his
friends you can do this...
SELECT  f.FID, p.Name
FROM Friend f JOIN People p ON f.FID = p.PID
WHERE f.PID = 234

of course you have the problem where john has Joe as a friend but Joe
doesn't have john as a friend.  This seeming inconsistency, may or may
not be a problem depending on exactly what kind of a relationship you
are trying to define.

--
Chris W
KE5GIX

Gift Giving Made Easy
Get the gifts you want &
give the gifts they want
One stop wish list for any gift,
from anywhere, for any occasion!
http://thewishzone.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to