summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2010-03-09 19:40:41 +0100
committerYorhel <git@yorhel.nl>2010-03-09 19:40:41 +0100
commitf72772a0e741399e2fcb3b83f54d0d8ac52b4d2c (patch)
treed503e13dedf98483f7a1ba4126b79d91eca0abb2
parent736281e86b70c2ddc65730b6e4c366b4d4c9921f (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.pm4
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;