diff options
author | Yorhel <git@yorhel.nl> | 2009-11-29 19:03:42 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2009-11-29 19:09:36 +0100 |
commit | fbcadd3ceb50a97dab4dd5cd284cc35e8b89b3b4 (patch) | |
tree | 99996735dd5878780bdf9c2422efae3c8cc6459a /lib/VNDB/DB/VN.pm | |
parent | 11290678d9b900baec7a92581a6f87cc70d329ea (diff) |
SQL: Replace index on tags_vn_inherit (tag) with an index on (tag,vid)
This drastically improves the performance of the search-VN-tag-filter
feature, and it seems PostgreSQL can use the index even when only
filtering results by the tag column.
Diffstat (limited to 'lib/VNDB/DB/VN.pm')
-rw-r--r-- | lib/VNDB/DB/VN.pm | 1 |
1 files changed, 1 insertions, 0 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| : (), ); |