slow query w/ an inner join

MySQL & MariaDB Other MySQL® Questions
1

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.