Performance: Optimize Comments admin page

c.name AS registered_name and u.name AS registered_name are definitely not the same. That's why it is needed to join the users table. Not sure how that registered name is used down the road and if we can avoid it.

Crap. This is *again* a case where the MySQL query planner gets dumb:

mysql> EXPLAIN SELECT c.subject, c.nid, c.cid, c.comment, c.timestamp, c.status, c.name, c.homepage, u.name AS registered_name, u.uid, n.title as node_title FROM comments c INNER JOIN users u ON u.uid = c.uid INNER JOIN node n ON n.nid = c.nid WHERE c.status = 0 ORDER BY timestamp DESC LIMIT 0, 10;
+----+-------------+-------+--------+-----------------------------------------------------------------------------+-----------------+---------+--------------------+--------+----------------------------------------------+
| id | select_type | table | type   | possible_keys                                                               | key             | key_len | ref                | rows   | Extra                                        |
+----+-------------+-------+--------+-----------------------------------------------------------------------------+-----------------+---------+--------------------+--------+----------------------------------------------+
|  1 | SIMPLE      | n     | index  | PRIMARY,nid                                                                 | node_title_type | 781     | NULL               | 589910 | Using index; Using temporary; Using filesort | 
|  1 | SIMPLE      | c     | ref    | tracker_user,tracker_global,tracker_changed,tracker_subscription,status,nid | tracker_changed | 5       | drupal.n.nid,const |      2 | Using where                                  | 
|  1 | SIMPLE      | u     | eq_ref | PRIMARY                                                                     | PRIMARY         | 4       | drupal.c.uid       |      1 | Using where                                  | 
+----+-------------+-------+--------+-----------------------------------------------------------------------------+-----------------+---------+--------------------+--------+----------------------------------------------+
3 rows in set (0.00 sec)

Versus:

mysql> EXPLAIN SELECT c.subject, c.nid, c.cid, c.comment, c.timestamp, c.status, c.name, c.homepage, u.name AS registered_name, u.uid, n.title as node_title FROM comments c INNER JOIN users u ON u.uid = c.uid LEFT JOIN node n ON n.nid = c.nid WHERE c.status = 0 ORDER BY timestamp DESC LIMIT 0, 10;
+----+-------------+-------+--------+---------------------------------------------------------+----------------+---------+--------------+---------+-------------+
| id | select_type | table | type   | possible_keys                                           | key            | key_len | ref          | rows    | Extra       |
+----+-------------+-------+--------+---------------------------------------------------------+----------------+---------+--------------+---------+-------------+
|  1 | SIMPLE      | c     | ref    | tracker_user,tracker_global,tracker_subscription,status | tracker_global | 1       | const        | 1168020 | Using where | 
|  1 | SIMPLE      | u     | eq_ref | PRIMARY                                                 | PRIMARY        | 4       | drupal.c.uid |       1 | Using where | 
|  1 | SIMPLE      | n     | eq_ref | PRIMARY,nid                                             | PRIMARY        | 4       | drupal.c.nid |       1 |             | 
+----+-------------+-------+--------+---------------------------------------------------------+----------------+---------+--------------+---------+-------------+
3 rows in set (0.00 sec)

When the query is symmetric (all INNER JOINs), the query planner can order the tables the way it wants to. For some reason, it thinks that it will be easier to start from the {node} table, and get stuck with no index to do the sort on {comment}.timestamp. As soon as we make the query non-symmetric (by transforming the INNER JOIN to a LEFT JOIN), MySQL can satisfy the query from indexes alone.