From 22b6d2f30cb1d5a0e7b4f1385ddba79e59dc0b34 Mon Sep 17 00:00:00 2001 From: Dries Buytaert Date: Sat, 12 Jul 2003 22:21:55 +0000 Subject: - Performance improvement: changed a fair amount of "left joins" to "inner joins". Patch by Gerhard. --- modules/forum/forum.module | 26 +++++++++++++------------- 1 file changed, 13 insertions(+), 13 deletions(-) (limited to 'modules/forum') diff --git a/modules/forum/forum.module b/modules/forum/forum.module index 045c9a536..6d54c2d40 100644 --- a/modules/forum/forum.module +++ b/modules/forum/forum.module @@ -86,10 +86,10 @@ function forum_block($op = "list", $delta = 0) { if (empty($cache)) { unset($items); - $content = node_title_list(db_query_range("SELECT n.nid, n.title, u.uid, u.name, GREATEST(n.created, MAX(c.timestamp)) AS sort FROM {node} n LEFT JOIN {forum} f ON n.nid = f.nid LEFT JOIN {comments} c ON n.nid = c.nid LEFT JOIN {users} u ON n.uid = u.uid WHERE n.type = 'forum' AND n.nid = f.nid AND n.status = 1 GROUP BY n.nid, n.title, n.created, u.uid, u.name ORDER BY sort DESC", 0, variable_get("forum_block_num", "5")), t("Active forum topics:")); + $content = node_title_list(db_query_range("SELECT n.nid, n.title, u.uid, u.name, GREATEST(n.created, MAX(c.timestamp)) AS sort FROM {node} n INNER JOIN {forum} f ON n.nid = f.nid INNER JOIN {comments} c ON n.nid = c.nid INNER JOIN {users} u ON n.uid = u.uid WHERE n.type = 'forum' AND n.nid = f.nid AND n.status = 1 GROUP BY n.nid, n.title, n.created, u.uid, u.name ORDER BY sort DESC", 0, variable_get("forum_block_num", "5")), t("Active forum topics:")); unset ($items); - $content .= node_title_list(db_query_range("SELECT n.nid, n.title, u.uid, u.name FROM {node} n LEFT JOIN {forum} f ON n.nid = f.nid LEFT JOIN {users} u ON n.uid = u.uid WHERE n.type = 'forum' ORDER BY n.nid DESC", 0, variable_get("forum_block_num", "5")), t("New forum topics:")); + $content .= node_title_list(db_query_range("SELECT n.nid, n.title, u.uid, u.name FROM {node} n INNER JOIN {forum} f ON n.nid = f.nid INNER JOIN {users} u ON n.uid = u.uid WHERE n.type = 'forum' ORDER BY n.nid DESC", 0, variable_get("forum_block_num", "5")), t("New forum topics:")); if ($content) { $content .= "
". l(t("more"), "forum", array("title" => t("Read the latest forum topics."))) ."
"; @@ -121,7 +121,7 @@ function forum_link($type, $node = 0, $main = 0) { if (!$main && $type == "node" && $node->type == "forum") { // get previous and next topic - $result = db_query("SELECT n.nid, n.title, n.body, GREATEST(n.created, MAX(c.timestamp)) AS date_sort, COUNT(c.nid) AS num_comments FROM {node} n LEFT JOIN {forum} f ON n.nid = f.nid LEFT JOIN {comments} c ON n.nid = c.nid WHERE n.nid = f.nid AND f.tid = %d AND n.status = 1 GROUP BY n.nid, n.title, n.body, n.created ORDER BY ". _forum_get_topic_order(isset($user->sortby) ? $user->sortby : variable_get("forum_order",1)), $node->tid); + $result = db_query("SELECT n.nid, n.title, n.body, GREATEST(n.created, MAX(c.timestamp)) AS date_sort, COUNT(c.nid) AS num_comments FROM {node} n INNER JOIN {forum} f ON n.nid = f.nid INNER JOIN {comments} c ON n.nid = c.nid WHERE n.nid = f.nid AND f.tid = %d AND n.status = 1 GROUP BY n.nid, n.title, n.body, n.created ORDER BY ". _forum_get_topic_order(isset($user->sortby) ? $user->sortby : variable_get("forum_order",1)), $node->tid); while ($topic = db_fetch_object($result)) { if ($stop == 1) { @@ -280,7 +280,7 @@ function _forum_last_comment($nid) { } function _forum_last_reply($nid) { - $value = db_fetch_object(db_query_range("SELECT c.timestamp, u.name, u.uid FROM {comments} c LEFT JOIN {users} u ON c.uid = u.uid WHERE c.nid = %d AND c.status = 0 ORDER BY c.timestamp DESC", $nid, 0, 1)); + $value = db_fetch_object(db_query_range("SELECT c.timestamp, u.name, u.uid FROM {comments} c INNER JOIN {users} u ON c.uid = u.uid WHERE c.nid = %d AND c.status = 0 ORDER BY c.timestamp DESC", $nid, 0, 1)); return $value; } @@ -349,17 +349,17 @@ function forum_get_parents($tid) { } function _forum_num_topics($term) { - $value = db_fetch_object(db_query("SELECT COUNT(n.nid) AS count FROM {node} n LEFT JOIN {forum} f ON n.nid = f.nid WHERE f.tid = %d AND n.nid = f.nid AND n.status = 1 AND n.type = 'forum'", $term)); + $value = db_fetch_object(db_query("SELECT COUNT(n.nid) AS count FROM {node} n INNER JOIN {forum} f ON n.nid = f.nid WHERE f.tid = %d AND n.nid = f.nid AND n.status = 1 AND n.type = 'forum'", $term)); return ($value) ? $value->count : 0; } function _forum_num_replies($term) { - $value = db_fetch_object(db_query("SELECT COUNT(*) AS count FROM {comments} c LEFT JOIN {node} n ON n.nid = c.nid LEFT JOIN {forum} f ON n.nid = f.nid WHERE f.tid = %d AND n.nid = f.nid AND n.nid = c.nid AND n.status = 1 AND c.status = 0 AND n.type = 'forum'", $term)); + $value = db_fetch_object(db_query("SELECT COUNT(*) AS count FROM {comments} c INNER JOIN {node} n ON n.nid = c.nid INNER JOIN {forum} f ON n.nid = f.nid WHERE f.tid = %d AND n.nid = f.nid AND n.nid = c.nid AND n.status = 1 AND c.status = 0 AND n.type = 'forum'", $term)); return ($value) ? $value->count : 0; } function _forum_topics_read($uid) { - $result = db_query("SELECT tid, count(*) AS c FROM {history} h LEFT JOIN {node} n ON n.nid = h.nid LEFT JOIN {forum} f ON n.nid = f.nid WHERE f.nid = n.nid AND n.nid = h.nid AND n.type = 'forum' AND n.status = 1 AND h.uid = %d GROUP BY tid", $uid); + $result = db_query("SELECT tid, count(*) AS c FROM {history} h INNER JOIN {node} n ON n.nid = h.nid INNER JOIN {forum} f ON n.nid = f.nid WHERE f.nid = n.nid AND n.nid = h.nid AND n.type = 'forum' AND n.status = 1 AND h.uid = %d GROUP BY tid", $uid); while ($obj = db_fetch_object($result)) { $topics_read[$obj->tid] = $obj->c; @@ -369,9 +369,9 @@ function _forum_topics_read($uid) { } function _forum_last_post($term) { - $topic = db_fetch_object(db_query_range("SELECT n.nid, n.created AS timestamp, u.name AS name, u.uid AS uid FROM {forum} f LEFT JOIN {node} n ON n.nid = f.nid LEFT JOIN {users} u ON n.uid = u.uid WHERE f.tid = %d AND n.nid = f.nid AND n.type = 'forum' AND n.status = 1 ORDER BY timestamp DESC", $term, 0, 1)); + $topic = db_fetch_object(db_query_range("SELECT n.nid, n.created AS timestamp, u.name AS name, u.uid AS uid FROM {forum} f INNER JOIN {node} n ON n.nid = f.nid INNER JOIN {users} u ON n.uid = u.uid WHERE f.tid = %d AND n.nid = f.nid AND n.type = 'forum' AND n.status = 1 ORDER BY timestamp DESC", $term, 0, 1)); - $reply = db_fetch_object(db_query_range("SELECT n.nid, c.timestamp, u.name AS name, u.uid AS uid FROM {forum} f LEFT JOIN {node} n ON n.nid = f.nid LEFT JOIN {comments} c ON n.nid = c.nid LEFT JOIN {users} u ON c.uid = u.uid WHERE f.tid = %d AND n.nid = f.nid AND n.type = 'forum' AND n.status = 1 AND c.status = 0 ORDER BY c.timestamp DESC", $term, 0, 1)); + $reply = db_fetch_object(db_query_range("SELECT n.nid, c.timestamp, u.name AS name, u.uid AS uid FROM {forum} f INNER JOIN {node} n ON n.nid = f.nid INNER JOIN {comments} c ON n.nid = c.nid INNER JOIN {users} u ON c.uid = u.uid WHERE f.tid = %d AND n.nid = f.nid AND n.type = 'forum' AND n.status = 1 AND c.status = 0 ORDER BY c.timestamp DESC", $term, 0, 1)); $value = ($topic->timestamp > $reply->timestamp) ? $topic : $reply; @@ -387,9 +387,9 @@ function forum_get_topics($tid, $sortby, $forum_per_page) { $sql_sortby = _forum_get_topic_order($sortby); // show topics with the correct tid, or in the forum but with shadow = 1 - $sql = "SELECT n.nid, n.title, u.name AS name, u.uid AS uid, n.created AS timestamp, GREATEST(n.created, MAX(c.timestamp)) AS date_sort, COUNT(c.nid) AS num_comments, f.icon, n.comment AS comment_mode, f.tid FROM {node} n LEFT JOIN {term_node} r ON n.nid = r.nid LEFT JOIN {users} u ON n.uid = u.uid LEFT JOIN {comments} c ON n.nid = c.nid LEFT JOIN {forum} f ON n.nid = f.nid WHERE n.nid = r.nid AND ((r.tid = '".check_query($tid)."' AND f.shadow = 1) OR f.tid = '".check_query($tid)."') AND n.status = 1 AND n.type = 'forum' GROUP BY n.nid, n.title, u.name, u.uid, n.created, n.comment, f.tid, f.icon ORDER BY $sql_sortby"; + $sql = "SELECT n.nid, n.title, u.name AS name, u.uid AS uid, n.created AS timestamp, GREATEST(n.created, MAX(c.timestamp)) AS date_sort, COUNT(c.nid) AS num_comments, f.icon, n.comment AS comment_mode, f.tid FROM {node} n INNER JOIN {term_node} r ON n.nid = r.nid INNER JOIN {users} u ON n.uid = u.uid INNER JOIN {comments} c ON n.nid = c.nid INNER JOIN {forum} f ON n.nid = f.nid WHERE n.nid = r.nid AND ((r.tid = '".check_query($tid)."' AND f.shadow = 1) OR f.tid = '".check_query($tid)."') AND n.status = 1 AND n.type = 'forum' GROUP BY n.nid, n.title, u.name, u.uid, n.created, n.comment, f.tid, f.icon ORDER BY $sql_sortby"; - $sql_count = "SELECT COUNT(DISTINCT(n.nid)) FROM {node} n LEFT JOIN {forum} f ON n.nid = f.nid LEFT JOIN {term_node} r ON n.nid = r.nid WHERE n.nid = r.nid AND ( (r.tid = '".check_query($tid)."' AND f.shadow = 1) OR f.tid = '".check_query($tid)."') AND n.status = 1 AND n.type = 'forum'"; + $sql_count = "SELECT COUNT(DISTINCT(n.nid)) FROM {node} n INNER JOIN {forum} f ON n.nid = f.nid INNER JOIN {term_node} r ON n.nid = r.nid WHERE n.nid = r.nid AND ( (r.tid = '".check_query($tid)."' AND f.shadow = 1) OR f.tid = '".check_query($tid)."') AND n.status = 1 AND n.type = 'forum'"; $result = pager_query($sql, $forum_per_page, 0, $sql_count); $topic_num = db_num_rows($result); @@ -408,7 +408,7 @@ function forum_get_topics($tid, $sortby, $forum_per_page) { $topic->new = 1; } else { - $comments = db_result(db_query("SELECT COUNT(c.nid) FROM {node} n LEFT JOIN {comments} c ON n.nid = c.nid WHERE n.nid = '$topic->nid' AND n.status = 1 AND c.status = 0 AND timestamp > '$history' GROUP BY n.nid")); + $comments = db_result(db_query("SELECT COUNT(c.nid) FROM {node} n INNER JOIN {comments} c ON n.nid = c.nid WHERE n.nid = '$topic->nid' AND n.status = 1 AND c.status = 0 AND timestamp > '$history' GROUP BY n.nid")); $topic->new_replies = $comments ? $comments : 0; if ($topic->new_replies) { @@ -440,7 +440,7 @@ function _forum_new($tid) { $read[] = $r->nid; } - $nid = db_result(db_query_range("SELECT n.nid FROM {node} n LEFT JOIN {forum} f ON n.nid = f.nid WHERE n.type = 'forum' AND f.nid = n.nid AND n.status = 1 AND f.tid = %d ".($read ? "AND NOT (n.nid IN (".implode(",", $read).")) " : "") ."ORDER BY created", $tid, 0, 1)); + $nid = db_result(db_query_range("SELECT n.nid FROM {node} n INNER JOIN {forum} f ON n.nid = f.nid WHERE n.type = 'forum' AND f.nid = n.nid AND n.status = 1 AND f.tid = %d ".($read ? "AND NOT (n.nid IN (".implode(",", $read).")) " : "") ."ORDER BY created", $tid, 0, 1)); return $nid ? $nid : 0; } -- cgit v1.2.3