diff options
author | Yorhel <git@yorhel.nl> | 2010-11-07 11:31:01 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2010-11-07 11:31:01 +0100 |
commit | d4d5b3c13227a9d773843b8ca8550403debe5a1a (patch) | |
tree | 67340454d13cf1183d17a3e42b8511e62c88722b /lib/VNDB/DB/VN.pm | |
parent | ab64b573846da39622b8d430b079d7e8806a35d3 (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/VNDB/DB/VN.pm')
-rw-r--r-- | lib/VNDB/DB/VN.pm | 8 |
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 |