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 | userid21 23 14 1entries----------------entryID | userid | title1 1 title12 2 title23 2 title34 4 title45 3 title5mysql> 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!