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 | |+----------+------------------+------+-----+---------+----------------+
# 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 | |+----------+------------------+------+-----+---------+----------------+
Comment