Order By rand() Reduce Time - MySQL Snipplr Social Repository

Revision: 38947

at January 10, 2011 23:25 by aamirrajpoot

Initial Code

Original Query 

$totalrows = 10;

$sql = "SELECT 
 posts.Tags as tags, 
 posts.OwnerUserId as postsid, 
 posts.Id as postid, 
 posts.Body as body, 
 posts.Title as title, 
 users.Id as userid, 
 users.DisplayName as usersname  
FROM posts 
JOIN users ON posts.OwnerUserId = users.Id 
WHERE posts.Title != '' order by rand() asc limit " .  $totalrows;

$r = mysql_query($sql) or die(mysql_error());



Modified Version

$totalrows = 10;

$sql = "SELECT 
 posts.Tags as tags, 
 posts.OwnerUserId as postsid, 
 posts.Id as postid, 
 posts.Body as body, 
 posts.Title as title, 
 users.Id as userid, 
 users.DisplayName as usersname  
FROM posts 
JOIN users ON posts.OwnerUserId = users.Id 
JOIN (select posts.id from posts where posts.title != '' order by rand() asc limit " . $totalrows .") AS tmp_result
ON (posts.Id = tmp_result.Id)";


$r = mysql_query($sql) or die(mysql_error());

Initial URL

Initial Description

A guy asked me to reduce the query execution time. He is getting 10 results but the query had joins which makes it time consuming.

Initial Title

Order By rand() Reduce Time

Initial Tags

Initial Language

MySQL

Choose a language for easy browsing: