optimize help for order by

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!

What is the time between these queries if you do not use the union

Also reformat the query to be like

(SELECT … ORDER BY col limit 10)
UNION
(SELECT … ORDER BY col limit 10)
ORDER BY col limit 10

And make sure inner selects are done by index, this will still use filesort in the union but it will be only done on 20 rows.