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 | |
| 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.
| -rw-r--r-- | ChangeLog | 2 | ||||
| -rw-r--r-- | lib/VNDB/DB/VN.pm | 8 |
2 files changed, 8 insertions, 2 deletions
@@ -3,7 +3,7 @@ cross-site request forgery vulnerabilities - URL change: /u/logout => /u$id/logout - Bugfix: delete/update all references when deleting a user - - Optimized random screenshot fetching query + - Optimized random screenshot and VN SQL queries 2.12 - 2010-11-03 - !scr command for Multi::IRC 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 |