GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

optimize help for order by

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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 | | 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!

  • #2
    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.

    Comment

    Working...
    X