SQL: Throwing around some indices

These indices provide a significant speed-up of /v+ and /u+ pages, and
improve some other stuff as well.

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));