From acd22833d74158d4b5320578987f372464bef7b7 Mon Sep 17 00:00:00 2001 From: Michael Andreen Date: Sat, 22 Nov 2008 15:47:50 +0100 Subject: [PATCH] Speedup some forum queries by caching information. --- database/forum.sql | 37 ++++++++++++++ lib/NDWeb/Controller/Forum.pm | 93 +++++++++++++++++++---------------- 2 files changed, 87 insertions(+), 43 deletions(-) create mode 100644 database/forum.sql diff --git a/database/forum.sql b/database/forum.sql new file mode 100644 index 0000000..2cb34b7 --- /dev/null +++ b/database/forum.sql @@ -0,0 +1,37 @@ + +ALTER TABLE forum_threads ADD posts INTEGER NOT NULL DEFAULT 0; +ALTER TABLE forum_threads ADD mtime TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(); +ALTER TABLE forum_threads ADD ctime TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(); + + +CREATE OR REPLACE FUNCTION update_forum_thread_posts() RETURNS trigger + AS $$ +BEGIN + + IF TG_OP = 'INSERT' THEN + UPDATE forum_threads SET posts = posts + 1, mtime = GREATEST(NEW.time,mtime) + WHERE ftid = NEW.ftid; + ELSIF TG_OP = 'DELETE' THEN + UPDATE forum_threads SET posts = posts - 1 WHERE ftid = OLD.ftid; + ELSIF TG_OP = 'UPDATE' AND NEW.ftid <> OLD.ftid THEN + UPDATE forum_threads SET posts = posts - 1 WHERE ftid = OLD.ftid; + UPDATE forum_threads SET posts = posts + 1, mtime = GREATEST(NEW.time,mtime) + WHERE ftid = NEW.ftid; + END IF; + + return NEW; +END; +$$ + LANGUAGE plpgsql; + + +CREATE TRIGGER update_forum_thread_posts + AFTER INSERT OR UPDATE OR DELETE ON forum_posts + FOR EACH ROW + EXECUTE PROCEDURE update_forum_thread_posts(); + +UPDATE forum_threads ft SET posts = p.posts, mtime = p.time, ctime = p.ctime + FROM (SELECT ftid, count(fpid) AS posts, max(time) AS time, min(time) AS ctime + FROM forum_posts group by ftid) p + WHERE p.ftid = ft.ftid; + diff --git a/lib/NDWeb/Controller/Forum.pm b/lib/NDWeb/Controller/Forum.pm index a9b1cb5..a9a1f0d 100644 --- a/lib/NDWeb/Controller/Forum.pm +++ b/lib/NDWeb/Controller/Forum.pm @@ -26,19 +26,22 @@ sub index :Path :Args(0) { my ( $self, $c ) = @_; my $dbh = $c->model; - my $boards = $dbh->prepare(q{SELECT fcid,category,fb.fbid,fb.board - ,count(NULLIF(COALESCE(fp.fpid::BOOLEAN,FALSE) - AND COALESCE(fp.time > ftv.time,TRUE),FALSE)) AS unread - ,date_trunc('seconds',max(fp.time)::timestamp) as last_post - FROM forum_categories - JOIN forum_boards fb USING (fcid) - LEFT OUTER JOIN forum_threads ft USING (fbid) - LEFT OUTER JOIN forum_posts fp USING (ftid) - LEFT OUTER JOIN (SELECT * FROM forum_thread_visits WHERE uid = $1) ftv USING (ftid) - WHERE EXISTS (SELECT fbid FROM forum_access WHERE fbid = fb.fbid AND gid IN (SELECT groups($1))) - OR ft.ftid IN (SELECT ftid FROM forum_priv_access WHERE uid = $1) - GROUP BY fcid,category,fb.fbid, fb.board - ORDER BY fcid,fb.fbid + my $boards = $dbh->prepare(q{ +SELECT fcid,category,fb.fbid,fb.board + ,SUM((SELECT count(*) FROM forum_posts WHERE ftid = ft.ftid + AND COALESCE(time > ftv.time,TRUE))) AS unread + ,date_trunc('seconds',max(ft.mtime)::timestamp ) AS last_post +FROM forum_categories fc + JOIN forum_boards fb USING (fcid) + JOIN forum_threads ft USING (fbid) + LEFT OUTER JOIN (SELECT * FROM forum_thread_visits WHERE uid = $1) + ftv USING (ftid) +WHERE fbid IN (SELECT fbid FROM forum_access + WHERE gid IN (SELECT groups($1))) + OR ftid IN (SELECT ftid FROM forum_priv_access + WHERE uid = $1) +GROUP BY fcid,category,fb.fbid, fb.board +ORDER BY fcid,fb.fbid }); $boards->execute($c->stash->{UID}); @@ -58,22 +61,24 @@ sub allUnread : Local { my ( $self, $c ) = @_; my $dbh = $c->model; - my $threads = $dbh->prepare(q{SELECT fcid,category,fbid,board,ft.ftid,u.username,ft.subject, - count(NULLIF(COALESCE(fp.time > ftv.time,TRUE),FALSE)) AS unread,count(fp.fpid) AS posts, - date_trunc('seconds',max(fp.time)::timestamp) as last_post, - min(fp.time)::date as posting_date, ft.sticky - FROM forum_categories fc - JOIN forum_boards fb USING (fcid) - JOIN forum_threads ft USING (fbid) - JOIN forum_posts fp USING (ftid) - JOIN users u ON u.uid = ft.uid - LEFT OUTER JOIN (SELECT * FROM forum_thread_visits WHERE uid = $1) ftv ON ftv.ftid = ft.ftid - WHERE (fbid > 0 AND - fb.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) - GROUP BY fcid,category,fbid,board,ft.ftid, ft.subject,ft.sticky,u.username - HAVING count(NULLIF(COALESCE(fp.time > ftv.time,TRUE),FALSE)) >= 1 - ORDER BY fcid,fbid,sticky DESC,last_post DESC + my $threads = $dbh->prepare(q{ +SELECT fcid,category,fbid,board,ft.ftid,u.username,ft.subject,ft.sticky + ,(SELECT count(*) FROM forum_posts WHERE ftid = ft.ftid + AND COALESCE(time > ftv.time,TRUE)) AS unread + ,ft.posts,date_trunc('seconds',ft.mtime::timestamp) as last_post + ,ft.ctime::DATE as posting_date +FROM forum_categories fc + JOIN forum_boards fb USING (fcid) + JOIN forum_threads ft USING (fbid) + JOIN users u ON u.uid = ft.uid + LEFT OUTER JOIN (SELECT * FROM forum_thread_visits WHERE uid = $1) + ftv ON ftv.ftid = ft.ftid +WHERE COALESCE(ft.mtime > ftv.time,TRUE) + AND ft.ftid IN (SELECT ftid FROM forum_posts WHERE ftid = ft.ftid) + AND ((fbid > 0 AND + fb.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)) +ORDER BY fcid,fbid,sticky DESC,last_post DESC }); $threads->execute($c->stash->{UID}); @@ -163,20 +168,22 @@ sub board : Local { $c->detach('/default'); } - my $threads = $dbh->prepare(q{SELECT ft.ftid,u.username,ft.subject - ,count(NULLIF(COALESCE(fp.time > ftv.time,TRUE),FALSE)) AS unread,count(fp.fpid) AS posts - ,date_trunc('seconds',max(fp.time)::timestamp) as last_post - ,min(fp.time)::date as posting_date, ft.sticky - FROM forum_threads ft - JOIN forum_posts fp USING (ftid) - JOIN users u ON u.uid = ft.uid - LEFT OUTER JOIN (SELECT * FROM forum_thread_visits WHERE uid = $2) ftv ON ftv.ftid = ft.ftid - WHERE ft.fbid = $1 AND ( - ft.fbid IN (SELECT fbid FROM forum_access WHERE gid IN (SELECT groups($2))) - OR ft.ftid IN (SELECT ftid FROM forum_priv_access WHERE uid = $2) - ) - GROUP BY ft.ftid, ft.subject,ft.sticky,u.username - ORDER BY sticky DESC,last_post DESC + my $threads = $dbh->prepare(q{ +SELECT ft.ftid,u.username,ft.subject,ft.posts, ft.sticky + ,(SELECT count(*) FROM forum_posts WHERE ftid = ft.ftid + AND COALESCE(time > ftv.time,TRUE)) AS unread + ,ft.ctime::DATE as posting_date + ,date_trunc('seconds',ft.mtime::timestamp) as last_post +FROM forum_threads ft + JOIN users u USING(uid) + LEFT OUTER JOIN (SELECT * FROM forum_thread_visits WHERE uid = $2) + ftv ON ftv.ftid = ft.ftid +WHERE ft.posts > 0 AND ft.fbid = $1 AND ( + ft.fbid IN (SELECT fbid FROM forum_access WHERE gid IN (SELECT groups($2))) + OR ft.ftid IN (SELECT ftid FROM forum_priv_access WHERE uid = $2) + ) +GROUP BY ft.ftid, ft.subject,ft.sticky,u.username,ft.ctime,ft.mtime,ft.posts,ftv.time +ORDER BY sticky DESC,last_post DESC }); $threads->execute($board->{fbid},$c->stash->{UID}); my @threads; -- 2.39.2