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]
