From: Michael Andreen <harv@ruin.nu>
Date: Wed, 15 Jul 2009 21:19:32 +0000 (+0200)
Subject: Faster query for counting unread posts
X-Git-Url: https://ruin.nu/git/?p=ndwebbie.git;a=commitdiff_plain;h=de68127064e1584c95f2bc76e2f6853fcdc51542

Faster query for counting unread posts
---

diff --git a/database/priv_threads.sql b/database/priv_threads.sql
index 0d6ba65..8ab114f 100644
--- a/database/priv_threads.sql
+++ b/database/priv_threads.sql
@@ -9,14 +9,19 @@ CREATE TABLE forum_priv_access (
 
 CREATE OR REPLACE FUNCTION unread_posts(IN uid int, OUT unread int, OUT "new" int) AS
 $SQL$
-	SELECT count(*)::int AS unread, count(NULLIF(fp.time > (SELECT max(time)
-			FROM forum_thread_visits WHERE uid = $1),FALSE))::int AS new
-		FROM forum_threads ft
-			JOIN forum_posts fp USING (ftid)
-			LEFT OUTER JOIN (SELECT * FROM forum_thread_visits
-				WHERE uid = $1) ftv ON ftv.ftid = ft.ftid
-		WHERE (ftv.time IS NULL OR fp.time > ftv.time) AND (
-			(fbid > 0 AND fbid IN (SELECT fbid FROM forum_access
-				WHERE gid IN (SELECT groups($1)))
-			) OR ft.ftid IN (SELECT ftid FROM forum_priv_access WHERE uid = $1))
+SELECT count(*)::int AS unread
+	,count(NULLIF(fp.time > (SELECT max(time) FROM forum_thread_visits WHERE uid = $1),FALSE))::int AS new
+FROM(
+	SELECT ftid, ftv.time
+	FROM forum_threads ft
+		LEFT OUTER JOIN (SELECT * FROM forum_thread_visits WHERE uid = $1)
+			ftv USING (ftid)
+	WHERE COALESCE(ft.mtime > ftv.time,TRUE)
+		AND ((fbid > 0 AND
+				fbid IN (SELECT fbid FROM forum_access WHERE gid IN (SELECT groups($1)))
+			) OR ft.ftid IN (SELECT ftid FROM forum_priv_access WHERE uid = $1)
+		)
+	) ft
+	JOIN forum_posts fp USING (ftid)
+WHERE COALESCE(fp.time > ft.time, TRUE)
 $SQL$ LANGUAGE sql STABLE;