diff options
-rw-r--r-- | lib/VNDB/DB/VN.pm | 1 | ||||
-rw-r--r-- | util/dump.sql | 4 | ||||
-rw-r--r-- | util/updates/update_2.10.sql | 4 |
3 files changed, 5 insertions, 4 deletions
diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm index feaa9bd0..8bd7b0f8 100644 --- a/lib/VNDB/DB/VN.pm +++ b/lib/VNDB/DB/VN.pm @@ -93,6 +93,7 @@ sub dbVNGet { '(SELECT COUNT(*)+1 FROM vn iv WHERE iv.hidden = false AND iv.c_popularity > v.c_popularity) AS p_ranking', '(SELECT COUNT(*)+1 FROM vn iv WHERE iv.hidden = false AND iv.c_rating > v.c_rating) AS r_ranking', ) : (), + # TODO: optimize this, as it will be very slow when the selected tags match a lot of VNs (>1000) $tag_ids ? qq|(SELECT AVG(tvh.rating) FROM tags_vn_inherit tvh WHERE tvh.tag IN($tag_ids) AND tvh.vid = v.id AND spoiler <= $o{tags_include}[0] GROUP BY tvh.vid) AS tagscore| : (), ); diff --git a/util/dump.sql b/util/dump.sql index bf13c115..cf8821ec 100644 --- a/util/dump.sql +++ b/util/dump.sql @@ -494,7 +494,7 @@ $$ LANGUAGE plpgsql; -- recalculate tags_vn_inherit CREATE OR REPLACE FUNCTION tag_vn_calc() RETURNS void AS $$ BEGIN - DROP INDEX IF EXISTS tags_vn_inherit_tag; + DROP INDEX IF EXISTS tags_vn_inherit_tag_vid; TRUNCATE tags_vn_inherit; -- populate tags_vn_inherit INSERT INTO tags_vn_inherit @@ -524,7 +524,7 @@ BEGIN GROUP BY tag, vid HAVING AVG(vote) > 0; -- recreate index - CREATE INDEX tags_vn_inherit_tag ON tags_vn_inherit (tag); + CREATE INDEX tags_vn_inherit_tag_vid ON tags_vn_inherit (tag, vid); -- and update the VN count in the tags table UPDATE tags SET c_vns = (SELECT COUNT(*) FROM tags_vn_inherit WHERE tag = id); RETURN; diff --git a/util/updates/update_2.10.sql b/util/updates/update_2.10.sql index 04b37bbf..469cce49 100644 --- a/util/updates/update_2.10.sql +++ b/util/updates/update_2.10.sql @@ -13,7 +13,7 @@ CREATE TABLE tags_vn_inherit ( -- more efficient version of tag_vn_calc() CREATE OR REPLACE FUNCTION tag_vn_calc() RETURNS void AS $$ BEGIN - DROP INDEX IF EXISTS tags_vn_inherit_tag; + DROP INDEX IF EXISTS tags_vn_inherit_tag_vid; TRUNCATE tags_vn_inherit; -- populate tags_vn_inherit INSERT INTO tags_vn_inherit @@ -43,7 +43,7 @@ BEGIN GROUP BY tag, vid HAVING AVG(vote) > 0; -- recreate index - CREATE INDEX tags_vn_inherit_tag ON tags_vn_inherit (tag); + CREATE INDEX tags_vn_inherit_tag_vid ON tags_vn_inherit (tag, vid); -- and update the VN count in the tags table UPDATE tags SET c_vns = (SELECT COUNT(*) FROM tags_vn_inherit WHERE tag = id); RETURN; |