summaryrefslogtreecommitdiff
path: root/lib
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2010-11-07 11:31:01 +0100
committerYorhel <git@yorhel.nl>2010-11-07 11:31:01 +0100
commitd4d5b3c13227a9d773843b8ca8550403debe5a1a (patch)
tree67340454d13cf1183d17a3e42b8511e62c88722b /lib
parentab64b573846da39622b8d430b079d7e8806a35d3 (diff)
Optimized random VN fetching query
Similar to ab64b573846da39622b8d430b079d7e8806a35d3, but with a few more constraints as dbVNGet() is a more generic function. This and the other commit greatly improve the page generation time of the homepage. From ~250ms to ~110ms in my tests.
Diffstat (limited to 'lib')
-rw-r--r--lib/VNDB/DB/VN.pm8
1 files changed, 7 insertions, 1 deletions
diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm
index adee8aff..0088d18f 100644
--- a/lib/VNDB/DB/VN.pm
+++ b/lib/VNDB/DB/VN.pm
@@ -18,6 +18,7 @@ sub dbVNGet {
$o{results} ||= 10;
$o{page} ||= 1;
$o{what} ||= '';
+ $o{sort} ||= 'title';
my @where = (
$o{id} ? (
@@ -43,6 +44,11 @@ sub dbVNGet {
# don't fetch hidden items unless we ask for an ID
!$o{id} && !$o{rev} ? (
'v.hidden = FALSE' => 0 ) : (),
+ # optimize fetching random entries (only when there are no other filters present, otherwise this won't work well)
+ $o{sort} eq 'rand' && $o{results} <= 10 && !grep(!/^(?:results|page|what|sort)$/, keys %o) ? (
+ sprintf 'v.id IN(SELECT floor(random() * last_value)::integer
+ FROM generate_series(1,20), (SELECT last_value FROM vn_id_seq) s1
+ LIMIT 20)' ) : (),
);
my @join = (
@@ -83,7 +89,7 @@ sub dbVNGet {
title => 'title %s',
tagscore => 'tagscore %s',
rand => 'RANDOM()',
- }->{ $o{sort}||'title' }, $o{reverse} ? 'DESC' : 'ASC';
+ }->{$o{sort}}, $o{reverse} ? 'DESC' : 'ASC';
my($r, $np) = $self->dbPage(\%o, q|
SELECT !s