diff options
author | Yorhel <git@yorhel.nl> | 2016-07-31 10:12:34 +0200 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2016-07-31 10:12:34 +0200 |
commit | 7b5e17826e67258d7c42f9fdb483f3b65ed31ab9 (patch) | |
tree | d26574d3b98b3e55dcf65e444363ba372b0f7078 /util | |
parent | a8a9a2a12d95e0a443f4b768698d50f4fc4e8fff (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.
Diffstat (limited to 'util')
-rw-r--r-- | util/sql/schema.sql | 7 | ||||
-rw-r--r-- | util/sql/tableattrs.sql | 3 | ||||
-rw-r--r-- | 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); |