Faster query for counting unread posts

diff --git a/database/priv_threads.sql b/database/priv_threads.sql
index 0d6ba651516888d7562ff0f3508c8d733f08dea2..8ab114f4e582af5ca56f55b9e8f7682aec43fbc6 100644 (file)
--- 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;