diff options
author | Yorhel <git@yorhel.nl> | 2010-11-07 11:01:17 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2010-11-07 11:01:17 +0100 |
commit | ab64b573846da39622b8d430b079d7e8806a35d3 (patch) | |
tree | a25c92d595655d21ad1ba0bf02842cf05de9e51e | |
parent | 466f45685e420574647eef75d32b83e1126c9e0a (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
-rw-r--r-- | ChangeLog | 1 | ||||
-rw-r--r-- | lib/VNDB/DB/VN.pm | 20 |
2 files changed, 13 insertions, 8 deletions
@@ -3,6 +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 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 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| ); } |