diff options
author | Yorhel <git@yorhel.nl> | 2010-03-09 19:40:41 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2010-03-09 19:40:41 +0100 |
commit | f72772a0e741399e2fcb3b83f54d0d8ac52b4d2c (patch) | |
tree | d503e13dedf98483f7a1ba4126b79d91eca0abb2 | |
parent | 736281e86b70c2ddc65730b6e4c366b4d4c9921f (diff) |
DB::ULists: Greatly improved query performance for VN list with 'hide voted'
Crawlers would often find such pages, and the query would often take
more than a second to finish, in some extreme cases even 10 seconds.
This fix converts an intermediate result into an array, forcing the
query planner to evaluate the subquery first, resulting in a far more
optimal query plan.
-rw-r--r-- | lib/VNDB/DB/ULists.pm | 4 |
1 files changed, 2 insertions, 2 deletions
diff --git a/lib/VNDB/DB/ULists.pm b/lib/VNDB/DB/ULists.pm index 19831593..e926bae4 100644 --- a/lib/VNDB/DB/ULists.pm +++ b/lib/VNDB/DB/ULists.pm @@ -47,13 +47,13 @@ sub dbVNListList { # construct the global WHERE clause my $where = $o{voted} != -1 ? 'vo.vote IS NOT NULL' : ''; - $where .= ($where?' OR ':'').q|v.id IN( + $where .= ($where?' OR ':'').q|v.id = ANY(ARRAY( SELECT irv.vid FROM rlists irl JOIN releases ir ON ir.id = irl.rid JOIN releases_vn irv ON irv.rid = ir.latest WHERE uid = ? - )| if $o{voted} != 1; + ))| if $o{voted} != 1; $where = '('.$where.') AND LOWER(SUBSTR(vr.title, 1, 1)) = \''.$o{char}.'\'' if $o{char}; $where = '('.$where.') AND (ASCII(vr.title) < 97 OR ASCII(vr.title) > 122) AND (ASCII(vr.title) < 65 OR ASCII(vr.title) > 90)' if defined $o{char} && !$o{char}; $where = '('.$where.') AND vo.vote IS NULL' if $o{voted} == -1; |