From 7b5e17826e67258d7c42f9fdb483f3b65ed31ab9 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sun, 31 Jul 2016 10:12:34 +0200 Subject: SQL: Improve trait cache update from 206 to 16 seconds VNDB tends to get unresponsive for a few minutes when the daily cron is run. This should help somewhat. --- util/sql/schema.sql | 7 +++++-- util/sql/tableattrs.sql | 3 +-- util/updates/update_2.26.sql | 6 ++++++ 3 files changed, 12 insertions(+), 4 deletions(-) diff --git a/util/sql/schema.sql b/util/sql/schema.sql index 9d71d7b8..b641c556 100644 --- a/util/sql/schema.sql +++ b/util/sql/schema.sql @@ -545,9 +545,12 @@ CREATE TABLE traits ( ); -- traits_chars +-- This table is a cache for the data in chars_traits and includes child traits +-- into parent traits. In order to improve performance, there are no foreign +-- key constraints on this table. CREATE TABLE traits_chars ( - cid integer NOT NULL, - tid integer NOT NULL, + cid integer NOT NULL, -- chars (id) + tid integer NOT NULL, -- traits (id) spoil smallint NOT NULL DEFAULT 0, PRIMARY KEY(cid, tid) ); diff --git a/util/sql/tableattrs.sql b/util/sql/tableattrs.sql index 55f91742..705954fe 100644 --- a/util/sql/tableattrs.sql +++ b/util/sql/tableattrs.sql @@ -62,8 +62,6 @@ ALTER TABLE traits ADD CONSTRAINT traits_addedby_fkey ALTER TABLE traits ADD CONSTRAINT traits_group_fkey FOREIGN KEY ("group") REFERENCES traits (id); ALTER TABLE traits_parents ADD CONSTRAINT traits_parents_trait_fkey FOREIGN KEY (trait) REFERENCES traits (id); ALTER TABLE traits_parents ADD CONSTRAINT traits_parents_parent_fkey FOREIGN KEY (parent) REFERENCES traits (id); -ALTER TABLE traits_chars ADD CONSTRAINT traits_chars_cid_fkey FOREIGN KEY (cid) REFERENCES chars (id); -ALTER TABLE traits_chars ADD CONSTRAINT traits_chars_tid_fkey FOREIGN KEY (tid) REFERENCES traits (id); ALTER TABLE users_prefs ADD CONSTRAINT users_prefs_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; ALTER TABLE vn ADD CONSTRAINT vn_rgraph_fkey FOREIGN KEY (rgraph) REFERENCES relgraphs (id); ALTER TABLE vn_hist ADD CONSTRAINT vn_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id); @@ -111,6 +109,7 @@ CREATE INDEX tags_vn_vid ON tags_vn (vid); CREATE INDEX threads_posts_date ON threads_posts (date); CREATE INDEX threads_posts_ts ON threads_posts USING gin(bb_tsvector(msg)); CREATE INDEX threads_posts_uid ON threads_posts (uid); -- Only really used on /u+ pages to get stats +CREATE INDEX traits_chars_tid ON traits_chars (tid); -- Significantly speeds up traits_chars_calc() and possibly other stuff CREATE INDEX vn_seiyuu_aid ON vn_seiyuu (aid); -- Only used on /s+? CREATE INDEX vn_seiyuu_cid ON vn_seiyuu (cid); -- Only used on /c+? CREATE INDEX vn_staff_aid ON vn_staff (aid); diff --git a/util/updates/update_2.26.sql b/util/updates/update_2.26.sql index 20293878..b73936ad 100644 --- a/util/updates/update_2.26.sql +++ b/util/updates/update_2.26.sql @@ -48,3 +48,9 @@ $$ LANGUAGE SQL; -- Thai language ALTER TYPE language ADD VALUE 'th' BEFORE 'tr'; + + +-- Improve performance for traits_chars calculation +ALTER TABLE traits_chars DROP CONSTRAINT traits_chars_cid_fkey; +ALTER TABLE traits_chars DROP CONSTRAINT traits_chars_tid_fkey; +CREATE INDEX traits_chars_tid ON traits_chars (tid); -- cgit v1.2.3