Earl Miles wrote:<br><br>template_preprocess_forum<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">_topic_navigation (formerly<br>theme_forum_topic_navigation) has this *really* awful query in it:
</blockquote><div><br>[..] <br></div><br><br>Here's the challenge: Write the patch to fix this, cause this needs to happen.<br>I checked the issue queue and I don't see an issue for it.<br><br>_________<br><br><br>
I think this is the issue (although nothing in there for that particular query).<br><br><a href="http://drupal.org/node/145353">http://drupal.org/node/145353</a> (forum performance improvements)<br><br>I have an abortive patch on there (the forum table disappeared as I was working on it) which was a start on:
<br><ul><li>forum_get_topics</li><li>forum_get_forums</li><li>forum_topics_unread</li></ul>Also: <a href="http://drupal.org/node/148849">http://drupal.org/node/148849</a> (merge {node_comment_statistics} and {node_counter} into {node}
<br><br>which'd affect that query.<br><br>catch<br><h1 class="title node-type-project_issue"><br></h1><br><div><span class="gmail_quote"></span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
There's a bug in forum.module that I wanted to fix this cycle, and I realize I<br>am not going to get to it. I've got entirely too many things on my plate (Views<br>2, I'm looking at you) and I need to divest myself of some of these things I
<br>want to do. However, this one is important.<br><br>template_preprocess_forum_topic_navigation (formerly<br>theme_forum_topic_navigation) has this *really* awful query in it:<br><br>function template_preprocess_forum_topic_navigation(&$variables) {
<br> $output = '';<br><br> // get previous and next topic<br> $sql = "SELECT n.nid, n.title, n.sticky, l.comment_count,<br>l.last_comment_timestamp FROM {node} n INNER JOIN {node_comment_statistics} l<br>
ON n.nid = l.nid INNER JOIN {term_node} r ON n.nid = r.nid AND r.tid = %d WHERE<br>n.status = 1 ORDER BY n.sticky DESC, ".<br>_forum_get_topic_order_sql(variable_get('forum_order', 1));<br> $result = db_query(db_rewrite_sql($sql), isset($variables['node']->tid) ?
<br>$variables['node']->tid : 0);<br><br> $stop = $variables['prev'] = $variables['next'] = 0;<br><br> while ($topic = db_fetch_object($result)) {<br> if ($stop == 1) {<br> $variables['next'] = $topic->nid;
<br> $variables['next_title'] = check_plain($topic->title);<br> $variables['next_url'] = url("node/$topic->nid");<br> break;<br> }<br> if ($topic->nid == $variables['node']->nid) {
<br> $stop = 1;<br> }<br> else {<br> $variables['prev'] = $topic->nid;<br> $variables['prev_title'] = check_plain($topic->title);<br> $variables['prev_url'] = url("node/$topic->nid");
<br> }<br> }<br>}<br><br>To summarize: It runs a query to find every post in the current forum, then<br>counts its way through to your current post, then looks 1 ahead to see what the<br>next/previous posts are. If you have thousands of topics in your forum, this is
<br>extremely inefficient.<br><br>This can be much more efficiently done in two queries where you do a limit 1<br>and do > on one and < on the other, using the sort criteria, to determine what<br>next/previous is.<br>
<br>For example, if you're sorting on created, you do a SELECT nid FROM node WHERE<br>[sufficient clauses to restrict to just this forum] WHERE node.created >=<br>$node->created AND node.nid <> $node->nid LIMIT 1
<br><br>And reverse it to find the previous.<br><br>Here's the challenge: Write the patch to fix this, cause this needs to happen.<br>I checked the issue queue and I don't see an issue for it.<br></blockquote></div>
<br>