order by rand() gets trouble for large posts

问题:

I have this code, this code gets trouble and so much time to be executed to show RANDOM posts from DB:

$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());

Please tell me what should I change to show random posts more quikly?

Regards,Dan

回答1:

rand() is very expensive. This will help: http://www.titov.net/2005/09/21/do-not-use-order-by-rand-or-how-to-get-random-rows-from-table/

回答2:

 $sql = "select posts.id from posts where posts.title != '' order by rand() asc limit " . $totalrows;

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

 $tmpArray = array();

 while($row = mysql_fetch_array($result)){
    $tmpArray[] = $row['id']; // This will add all items to array
 }

 $csvResult = implode(',',$tmpArray);

 $sql = " select posts.*, users.* From posts join users on posts.owneruserid  = user.id where posts.id in (" . $csvResult . ")";

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

Please check for any typos and column names.

回答3:

I don't have the means of testing this right now but it should give pretty uniform distribution, and I doubt speed will be an issue. Pick some random numbers ONCE in PHP and do some simple modulus operation that should be "random enough" to order your rows.

If the exact numbers I've chosen don't give good results you can probably choose some that will.

$totalrows = 10;  
$m = rand(31,61);
$k = randint(10,20);
$offset = rand(200,500) % $m;

$sql = ... "ORDER BY (post_id + $offset + $k*(post_id MOD 8)) MOD $m LIMIT $totalRows";

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

If this is still too slow you can try putting some basic randomness into the WHERE clause as a 'coarse' filter so not so many rows have to be ordered. However, keep in mind that it should be simpler than whatever you do in ORDER BY or you'll just be doing the same slow operations again.

Let me know if this works for you, im curious and wish I could test it myself in SQL. Distributions look pretty good with my trying something similar in Python for the set of 'id's 1-20000, but that doesnt tell me anything about speed of execution in SQL

回答4:

The problem with your code is.

1. You need to get 10 randome posts
2. You need to get the name of the person who posted
3. They should be random but the join makes it time taking.

I suggest you to split them,

$sql = "select posts.id where posts.title != '' order by rand() asc limit " . $totalrows;

execute the result and then implode the result in csv formate $csvResult = 2,3,4,5 these numbers are the IDs of the random posts. In your 2nd query

$sql = " select posts.*, users.* From posts join users on posts.owneruserid = user.id where posts.id in (" . $csvResult . ");

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

Hope this will reduce the time.

回答5:

One more way

$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());

Hmm I dont know if this will speed up your script :D it totally depends upon your records now.