diff options
author | Yorhel <git@yorhel.nl> | 2015-10-24 14:33:20 +0200 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2015-10-24 14:34:49 +0200 |
commit | bbe989de364ddc654bfc6385e22f1eaff23faad1 (patch) | |
tree | 53492ca720c207bc50472119e9e29ac566634794 | |
parent | 173ac8de5d96957dcc93fae5e36f6df2f9ce7118 (diff) |
SQL: Throwing around some indices
These indices provide a significant speed-up of /v+ and /u+ pages, and
improve some other stuff as well.
-rw-r--r-- | lib/VNDB/DB/ULists.pm | 4 | ||||
-rw-r--r-- | util/sql/tableattrs.sql | 10 |
2 files changed, 10 insertions, 4 deletions
diff --git a/lib/VNDB/DB/ULists.pm b/lib/VNDB/DB/ULists.pm index 23bde7cf..55ee9e37 100644 --- a/lib/VNDB/DB/ULists.pm +++ b/lib/VNDB/DB/ULists.pm @@ -238,11 +238,11 @@ sub dbVoteStats { my $u = $self->authInfo->{id}; my $r = [ map [0,0], 0..9 ]; $r->[$_->{idx}] = [ $_->{votes}, $_->{total} ] for (@{$self->dbAll(q| - SELECT (vote::numeric/10)::int-1 AS idx, COUNT(vote) as votes, SUM(vote) AS total + SELECT (vote::float/10)::int-1 AS idx, COUNT(vote) as votes, SUM(vote) AS total FROM votes !s !W - GROUP BY (vote::numeric/10)::int|, + GROUP BY (vote::float/10)::int|, $ign ? 'JOIN users ON id = uid AND (NOT ign_votes'.($u?sprintf(' OR id = %d',$u):'').')' : '', $col ? { '!s = ?' => [ $col, $id ] } : {}, )}); diff --git a/util/sql/tableattrs.sql b/util/sql/tableattrs.sql index 5b9ca1ae..7a144e1f 100644 --- a/util/sql/tableattrs.sql +++ b/util/sql/tableattrs.sql @@ -94,18 +94,24 @@ ALTER TABLE wlists ADD CONSTRAINT wlists_vid_fkey CREATE INDEX affiliate_links_rid ON affiliate_links (rid) WHERE NOT hidden; +CREATE INDEX chars_main ON chars (main) WHERE main IS NOT NULL AND NOT hidden; -- Only used on /c+ +CREATE INDEX chars_vns_vid ON chars_vns (vid); CREATE INDEX notifications_uid ON notifications (uid); CREATE INDEX releases_producers_pid ON releases_producers (pid); CREATE INDEX releases_vn_vid ON releases_vn (vid); CREATE INDEX staff_alias_id ON staff_alias (id); CREATE INDEX tags_vn_date ON tags_vn (date); CREATE INDEX tags_vn_inherit_tag_vid ON tags_vn_inherit (tag, vid); +CREATE INDEX tags_vn_uid ON tags_vn (uid); CREATE INDEX tags_vn_vid ON tags_vn (vid); -CREATE INDEX threads_posts_ts ON threads_posts USING gin(bb_tsvector(msg)); 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 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); -CREATE INDEX vn_seiyuu_aid ON vn_seiyuu (aid); +CREATE INDEX votes_date ON votes (date desc); -- Mainly used on /v+ pages, other pages don't really need it +CREATE INDEX votes_uid ON votes (uid); CREATE UNIQUE INDEX changes_itemrev ON changes (type, itemid, rev); CREATE UNIQUE INDEX chars_vns_pkey ON chars_vns (id, vid, COALESCE(rid, 0)); CREATE UNIQUE INDEX chars_vns_hist_pkey ON chars_vns_hist (chid, vid, COALESCE(rid, 0)); |