diff options
authorYorhel <>2016-07-31 10:12:34 +0200
committerYorhel <>2016-07-31 10:12:34 +0200
commit7b5e17826e67258d7c42f9fdb483f3b65ed31ab9 (patch)
parenta8a9a2a12d95e0a443f4b768698d50f4fc4e8fff (diff)
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.
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);