rendered paste body</div><div class="header"><a href="/node/105639#comment-245308" id="comment-245308" name="comment-245308" class="active">#73</a> submitted by <a href="/user/93254" title="View user profile.">David Strauss</a> on May 17, 2007 - 16:21 <span class="marker">updated</span></div><div class="content"><p>Global comments query:<br />
EXPLAIN SELECT n.nid, n.title, n.type, n.changed, n.uid, c.timestamp AS last_updated FROM comments c INNER JOIN node n ON n.nid = c.nid WHERE n.status = 1 AND c.status = 0 AND 1 ORDER BY last_updated DESC LIMIT 0, 1000;<br />
+----+-------------+-------+------+----------------------------------------------------+----------------+---------+-----------------+------+-------------+<br />
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |<br />
+----+-------------+-------+------+----------------------------------------------------+----------------+---------+-----------------+------+-------------+<br />
| 1 | SIMPLE | c | ref | lid,tracker_global | tracker_global | 1 | const | 359 | Using where |<br />
| 1 | SIMPLE | n | ref | PRIMARY,status,node_status_type,nid,tracker_global | PRIMARY | 4 | fk_portal.c.nid | 1 | Using where |<br />
+----+-------------+-------+------+----------------------------------------------------+----------------+---------+-----------------+------+-------------+</p>
<p>Global nodes query:<br />
EXPLAIN SELECT n.nid, n.title, n.type, n.changed, n.uid, n.changed AS last_updated FROM node n WHERE n.status = 1 AND 1 ORDER BY last_updated DESC;<br />
+----+-------------+-------+------+----------------------------------------+----------------+---------+-------+------+-------------+<br />
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |<br />
+----+-------------+-------+------+----------------------------------------+----------------+---------+-------+------+-------------+<br />
| 1 | SIMPLE | n | ref | status,node_status_type,tracker_global | tracker_global | 4 | const | 222 | Using where |<br />
+----+-------------+-------+------+----------------------------------------+----------------+---------+-------+------+-------------+<br />
1 row in set (0.00 sec)</p>
<p>User nodes query:<br />
EXPLAIN SELECT n.nid, n.title, n.type, n.changed, n.uid, n.changed AS last_updated FROM node n WHERE n.status = 1 AND n.uid = 1 ORDER BY last_updated DESC;<br />
+----+-------------+-------+------+---------------------------------------------------------+--------------+---------+-------------+------+-------------+<br />
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |<br />
+----+-------------+-------+------+---------------------------------------------------------+--------------+---------+-------------+------+-------------+<br />
| 1 | SIMPLE | n | ref | status,uid,node_status_type,tracker_user,tracker_global | tracker_user | 8 | const,const | 77 | Using where |<br />
+----+-------------+-------+------+---------------------------------------------------------+--------------+---------+-------------+------+-------------+<br />
1 row in set (0.00 sec)</p>
<p>User comments query:<br />
EXPLAIN SELECT n.nid, n.title, n.type, n.changed, n.uid, c.timestamp AS last_updated FROM comments c INNER JOIN node n ON n.nid = c.nid WHERE n.status = 1 AND c.status = 0 AND c.uid = 1 ORDER BY last_updated DESC;<br />
+----+-------------+-------+------+----------------------------------------------------+--------------+---------+-----------------+------+-------------+<br />
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |<br />
+----+-------------+-------+------+----------------------------------------------------+--------------+---------+-----------------+------+-------------+<br />
| 1 | SIMPLE | c | ref | lid,tracker_user,tracker_global | tracker_user | 5 | const,const | 47 | Using where |<br />
| 1 | SIMPLE | n | ref | PRIMARY,status,node_status_type,nid,tracker_global | nid | 4 | fk_portal.c.nid | 1 | Using where |<br />
+----+-------------+-------+------+----------------------------------------------------+--------------+---------+-----------------+------+-------------+<br />
2 rows in set (0.00 sec)</p>
<p>These are running on my company's portal, but the query plan shouldn't differ elsewhere.</p>