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 | |
+----------+------------------+------+-----+---------+----------------+
Members
Search
Help
Register
Login
Home
slow query w/ an inner join
