 slow query w/ an inner join [message #1142] |
Tue, 24 April 2007 15:47  |
 linuxrunner
Messages: 15
Registered: February 2007
Location: NYC |
Junior Member |
|
|
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 | |
+----------+------------------+------+-----+---------+----------------+
|
|
|
| Re: slow query w/ an inner join [message #1151 is a reply to message #1142 ] |
Wed, 25 April 2007 04:30  |
sterin
Messages: 324
Registered: March 2007
Location: Sweden |
Senior Member |
|
|
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.
|
|
|
Members
Search
Help
Register
Login
Home
