Announcement

Announcement Module
Collapse
No announcement yet.

slow query w/ an inner join

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

  • slow query w/ an inner join

    I'm new to query optimization and would love someone to explain why this query is soo slow and has to look at so many rows. Thanks for the help!

    # Query_time: 29 Lock_time: 0 Rows_sent: 10 Rows_examined: 529526
    SELECT c.nid, c.subject, c.cid, c.timestamp FROM comments c INNER JOIN node n ON n.nid = c.nid WHERE n.status = 1 AND c.status = 0 ORDER BY c.timestamp DESC LIMIT 0, 10;




    explain SELECT c.nid, c.subject, c.cid, c.timestamp FROM comments c INNER JOIN node n ON n.nid = c.nid WHERE n.status = 1 AND c.status = 0 ORDER BY c.timestamp DESC LIMIT 0, 10;+----+-------------+-------+------+-------------------------------------+--------+---------+-------------------------+------+----------------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+-------------------------------------+--------+---------+-------------------------+------+----------------------------------------------+| 1 | SIMPLE | n | ref | PRIMARY,status,node_status_type,nid | status | 4 | const | 3320 | Using index; Using temporary; Using filesort || 1 | SIMPLE | c | ref | lid | lid | 4 | mydrupal.n.nid | 21 | Using where |+----+-------------+-------+------+-------------------------------------+--------+---------+-------------------------+------+----------------------------------------------+



    Comments Table:

    +-----------+---------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-----------+---------------------+------+-----+---------+----------------+| cid | int(10) | NO | PRI | NULL | auto_increment || pid | int(10) | NO | | 0 | || nid | int(10) | NO | MUL | 0 | || uid | int(10) | NO | MUL | 0 | || subject | varchar(64) | NO | | | || comment | longtext | NO | | | || hostname | varchar(128) | NO | | | || timestamp | int(11) | NO | | 0 | || score | mediumint(9) | NO | | 0 | || status | tinyint(3) unsigned | NO | | 0 | || format | int(4) | NO | | 0 | || thread | varchar(255) | NO | | | || users | longtext | YES | | NULL | || name | varchar(60) | YES | | NULL | || mail | varchar(64) | YES | | NULL | || homepage | varchar(255) | YES | | NULL | |+-----------+---------------------+------+-----+---------+----------------+



    Node Table:

    +----------+------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+----------+------------------+------+-----+---------+----------------+| nid | int(10) unsigned | NO | PRI | NULL | auto_increment || vid | int(10) unsigned | NO | PRI | 0 | || type | varchar(32) | NO | MUL | | || title | varchar(128) | NO | MUL | | || uid | int(10) | NO | MUL | 0 | || status | int(4) | NO | MUL | 1 | || created | int(11) | NO | MUL | 0 | || changed | int(11) | NO | MUL | 0 | || comment | int(2) | NO | | 0 | || promote | int(2) | NO | MUL | 0 | || moderate | int(2) | NO | MUL | 0 | || sticky | int(2) | NO | | 0 | |+----------+------------------+------+-----+---------+----------------+

  • #2
    You do not have an index that mysql can use to solve your ORDER BY that is why you get "Using temporary; Using filesort".

    What happens then is that mysql will first create a temporary table of the result of the join and then it will need to sort it.
    Which can be a very costly operation depending on how many rows your join return.

    Create a combined index on comments(status, timestamp) that way mysql can use that index to find all matching rows in comments AND return them in the right order.
    Then you also create a combined index on node(nid, status).

    Then you should start to get some more speed out of this query.

    Comment

    Working...
    X