I have the following query that uses a filesort.. Its a bit condensed since I'm not putting in other fields I am grabbing.
It is a bit slow.. for people with a lot of friends.. it could take around 2-3 seconds.
SELECT title,entryid FROM members INNER JOIN friends ON members.id=userid1 INNER JOIN entries ON members.id=userid WHERE status=1 AND userid2=2
UNION
SELECT title,entryid FROM members INNER JOIN friends ON members.id=userid2 INNER JOIN entries ON members.id=userid WHERE status=1 AND userid1=2 ORDER BY entryid DESC LIMIT 30;
Here is the explain
mysql> explain SELECT title,entryid FROM members INNER JOIN friends ON members.id=userid1 INNER JOIN entries ON members.id=userid WHERE status=1 AND userid2=2 UNION SELECT title,entryid FROM members INNER JOIN friends ON members.id=userid2 INNER JOIN entries ON members.id=userid WHERE status=1 AND userid1=2 ORDER BY entryid DESC LIMIT 30;
+----+--------------+------------+--------+-----------------+---------+---------+---------------------------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------+--------+-----------------+---------+---------+---------------------------+------+----------------+
| 1 | PRIMARY | friends | ref | userid1,userid2 | userid2 | 3 | const | 96 | Using where |
| 1 | PRIMARY | members | eq_ref | PRIMARY | PRIMARY | 3 | photoblog.friends.userid1 | 2 | Using index |
| 1 | PRIMARY | entries | ref | userid | userid | 4 | photoblog.members.id | 11 | Using where |
| 2 | UNION | friends | ref | userid1,userid2 | userid1 | 3 | const | 354 | Using where |
| 2 | UNION | members | eq_ref | PRIMARY | PRIMARY | 3 | photoblog.friends.userid2 | 2 | Using index |
| 2 | UNION | entries | ref | userid | userid | 4 | photoblog.members.id | 11 | Using where |
| | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | Using filesort |
+----+--------------+------------+--------+-----------------+---------+---------+---------------------------+------+----------------+
Now my friends table looks a bit like this
friends
-------------
userid1 | userid2
1 2
3 1
4 1
entries
----------------
entryID | userid | title
1 1 title1
2 2 title2
3 2 title3
4 4 title4
5 3 title5
mysql> explain friends;
+-----------+-----------------------+------+-----+---------+ ----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-----------------------+------+-----+---------+ ----------------+
| id | mediumint(8) unsigned | NO | PRI | NULL | auto_increment |
| userid1 | mediumint(8) unsigned | NO | MUL | 0 | |
| userid2 | mediumint(8) unsigned | NO | MUL | 0 | |
| timestamp | int(10) unsigned | NO | | 0 | |
| status | smallint(1) unsigned | NO | | 0 | |
| reason | varchar(500) | NO | | NULL | |
+-----------+-----------------------+------+-----+---------+ ----------------+
6 rows in set (0.00 sec)
mysql> explain entries;
+----------+------------------------+------+-----+---------- --------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------------+------+-----+---------- --------+----------------+
| entryid | mediumint(10) unsigned | NO | PRI | NULL | auto_increment |
| userid | mediumint(8) unsigned | YES | MUL | NULL | |
| title | varchar(255) | YES | MUL | NULL | |
| photos | text | YES | | NULL | |
| sizes | mediumtext | NO | | NULL | |
| text | text | YES | | NULL | |
| category | int(6) unsigned | YES | | NULL | |
| created | int(10) unsigned | YES | MUL | NULL | |
| ts | int(10) unsigned | YES | MUL | 0 | |
| modified | int(10) unsigned | YES | | NULL | |
| date | date | NO | MUL | 0000-00-00 | |
| comments | smallint(3) unsigned | NO | | 1 | |
| views | mediumint(8) | NO | | 0 | |
| dir | varchar(10) | NO | | photos | |
| server | varchar(20) | NO | | i1.photoblog.com | |
| notes | longtext | NO | | NULL | |
| titles | text | NO | | NULL | |
+----------+------------------------+------+-----+---------- --------+----------------+
17 rows in set (0.01 sec)
Hope that helps.. I'm completely stuck!