Home » Performance » MySQL » optimize help for order by
optimize help for order by [message #1569] Thu, 02 August 2007 15:08 Go to next message
mzupan  is currently offline 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]

Re: optimize help for order by [message #1597 is a reply to message #1569 ] Thu, 16 August 2007 08:35 Go to previous message
Peter  is currently offline Peter
Messages: 405
Registered: August 2006
Senior Member
Super Guru
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.


Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
Previous Topic:Running query to update million records in the table - best way
Next Topic:Help.. How to communicate MySQL with RFID
Goto Forum:
  


Current Time: Mon Jul 6 22:03:56 EDT 2009

Total time taken to generate the page: 0.17220 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 2.7.7.
Copyright ©2001-2007 FUD Forum Bulletin Board Software

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.