speed up forum_get_forums and _forum_topics_unread

since http://drupal.org/node/105639 appears to be resolved just about, maybe it's time to look at some other expensive queries.

I reckon two of the big ones, especially for drupal.org will be forum_get_forums and _forum_topics_unread.

The comments on forum_get_forums appear to say it's keeping some bad syntax because of mysql 3.x - I'm guessing now drupal requires 4.x then these could be got rid of.

Here it is anyway:

// This query does not use full ANSI syntax since MySQL 3.x does not support
    // table1 INNER JOIN table2 INNER JOIN table3 ON table2_criteria ON table3_criteria
    // used to join node_comment_statistics to users.
    $sql = "SELECT ncs.last_comment_timestamp, IF (ncs.last_comment_uid != 0, u2.name, ncs.last_comment_name) AS last_comment_name, ncs.last_comment_uid FROM {node} n INNER JOIN {users} u1 ON n.uid = u1.uid INNER JOIN {term_node} tn ON n.nid = tn.nid INNER JOIN {node_comment_statistics} ncs ON n.nid = ncs.nid INNER JOIN {users} u2 ON ncs.last_comment_uid=u2.uid WHERE n.status = 1 AND n.type='forum' AND tn.tid = %d ORDER BY ncs.last_comment_timestamp DESC";
    $sql = db_rewrite_sql($sql);
    $topic = db_fetch_object(db_query_range($sql, $forum->tid, 0, 1));

It goes way over my head, but surely it's not necessary to join on {users} twice in the same query for a start?

Here's the first few lines of output from devel module on my forums index, we've got 35 forums so there's quite a few more queries like this running at 30-40ms each after the first biggies:

192.39	1	forum_get_forums	SELECT r.tid, COUNT(n.nid) AS topic_count, SUM(l.comment_count) AS comment_count FROM node n INNER JOIN node_comment_statistics l ON n.nid = l.nid INNER JOIN term_node r ON n.nid = r.nid WHERE n.status = 1 AND n.type = 'forum' GROUP BY r.tid
186.91	1	forum_get_forums	SELECT ncs.last_comment_timestamp, IF (ncs.last_comment_uid != 0, u2.name, ncs.last_comment_name) AS last_comment_name, ncs.last_comment_uid FROM node n INNER JOIN users u1 ON n.uid = u1.uid INNER JOIN term_node tn ON n.nid = tn.nid INNER JOIN node_comment_statistics ncs ON n.nid = ncs.nid INNER JOIN users u2 ON ncs.last_comment_uid=u2.uid WHERE n.status = 1 AND n.type='forum' AND tn.tid = 732 ORDER BY ncs.last_comment_timestamp DESC LIMIT 0, 1
138.36	1	_forum_topics_unread	SELECT COUNT(n.nid) FROM node n INNER JOIN term_node tn ON n.nid = tn.nid AND tn.tid = 732 LEFT JOIN history h ON n.nid = h.nid AND h.uid = 11 WHERE n.status = 1 AND n.type = 'forum' AND n.created > 1177103114 AND h.nid IS NULL