From d4d5b3c13227a9d773843b8ca8550403debe5a1a Mon Sep 17 00:00:00 2001
From: Yorhel <git@yorhel.nl>
Date: Sun, 7 Nov 2010 11:31:01 +0100
Subject: 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.
---
 lib/VNDB/DB/VN.pm | 8 +++++++-
 1 file changed, 7 insertions(+), 1 deletion(-)

(limited to 'lib')

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
-- 
cgit v1.2.3