summaryrefslogtreecommitdiff
path: root/lib/VNDB
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2010-11-07 11:01:17 +0100
committerYorhel <git@yorhel.nl>2010-11-07 11:01:17 +0100
commitab64b573846da39622b8d430b079d7e8806a35d3 (patch)
treea25c92d595655d21ad1ba0bf02842cf05de9e51e /lib/VNDB
parent466f45685e420574647eef75d32b83e1126c9e0a (diff)
Optimized random screenshot fetching query
By rewriting the query and using the trick documented here: http://blog.rhodiumtoad.org.uk/2009/03/08/selecting-random-rows-from-a-table/ Can be further optimized by putting an index on vn_screenshots.scr
Diffstat (limited to 'lib/VNDB')
-rw-r--r--lib/VNDB/DB/VN.pm20
1 files changed, 12 insertions, 8 deletions
diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm
index 394e8c06..adee8aff 100644
--- a/lib/VNDB/DB/VN.pm
+++ b/lib/VNDB/DB/VN.pm
@@ -205,14 +205,18 @@ sub dbScreenshotGet {
# Fetch random VN + screenshots
sub dbScreenshotRandom {
return shift->dbAll(q|
- SELECT vs.scr, vr.vid, vr.title, s.width, s.height
- FROM vn_screenshots vs
- JOIN vn v ON v.latest = vs.vid
- JOIN vn_rev vr ON vr.id = v.latest
- JOIN screenshots s ON vs.scr = s.id
- WHERE vs.nsfw = FALSE AND v.hidden = FALSE
- ORDER BY RANDOM()
- LIMIT 4|
+ SELECT s.id AS scr, s.width, s.height, vr.vid, vr.title
+ FROM screenshots s
+ JOIN vn_screenshots vs ON vs.scr = s.id
+ JOIN vn_rev vr ON vr.id = vs.vid
+ JOIN vn v ON v.id = vr.vid AND v.latest = vs.vid
+ WHERE NOT v.hidden AND NOT vs.nsfw
+ AND s.id IN(
+ SELECT floor(random() * last_value)::integer
+ FROM generate_series(1,20), (SELECT last_value FROM screenshots_id_seq) s1
+ LIMIT 20
+ )
+ LIMIT 4|
);
}