MySQL Performance: MySQL => optimize help for order by

optimize help for order by [message #1569]

Thu, 02 August 2007 15:08

mzupan
Messages: 4
Registered: August 2007

Junior Member

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!

[Updated on: Thu, 02 August 2007 15:09]

Report message to a moderator