Order by rand()

Jmz · 06-08-2008, 08:09 PM

I have a query that looks like:

Code:

SELECT * FROM tbl_gallery WHERE fld_userid = $UserID ORDER BY RAND() LIMIT 4

I've read that using rand() isn't a good idea as it takes a lot of system resources. How can I rewrite the query to do the same thing without putting too much strain on my server?

buggabill · 06-09-2008, 03:34 AM

In order to get four random rows you could do something like the following:

php Code:

<?php
    // get all of the ids for a user
    $result = mysql_query("SELECT id FROM tbl_gallery WHERE fld_userid = $UserID");
    
    // get the number of rows returned
    $numrows = mysql_num_rows($result);
    
    //build an array of the ids returned
    $idarr = array();
    
    while ($row = mysql_fetch_assoc($result))
    {
        $idarr[] = $row(id);
    }

    // build an array with 4 randomly selected ids in it
    $selidarr = array();
    
    for ($i=0;$i<=4;$i++)
    {
        $selidarr[] = $idarr[rand(0,$numrows)];
    }
    
    //convert the array into a string for use in a query
    $selidstr = implode(",", $selidarr);
    
    // run one more query to get the final desired results
    $sql = "SELECT * FROM tbl_gallery WHERE $id IN ($selidstr)"
?>

Hope this helps!

Jmz · 06-09-2008, 09:39 AM

Hi, thanks for that. I'm getting an error at the line:

Code:

$idarr[] = $row(id);

The error says:

Quote:

Fatal error: Call to undefined function: array() in /home/jmz360/public_html/play/photo/user/templates/0/gal.php on line 14

Any ideas how I fix it?

Jmz · 06-09-2008, 10:11 AM

Hi, thanks for that. I'm getting an error at the line:

Code:

$idarr[] = $row(id);

The error says:

Quote:

Fatal error: Call to undefined function: array() in /home/jmz360/public_html/play/photo/user/templates/0/gal.php on line 14

Any ideas how I fix it?

Also, I've noticed it can generate the same number more than once, how can I stop it doing this?

buggabill · 06-09-2008, 01:07 PM

Wow - I suck...

I wrote that real quickly on too little sleep...

Try this...

php Code:

$idarr[] = $row['id'];

Wildhoney · 06-10-2008, 07:24 PM

I can only give you the theory of how I did it. This isn't because I don't want to share it, but it has too many dependencies. What I did was the following:

Generate a random number using PHP. Select from the table where the ID is more or equal to;
Add that selected ID to an array and execute the first query;
When we loop again, follow the same process but append IN() to the end with the previously selected IDs;
Keep doing this until we get the amount we require.

As this is selecting an ID, which should be the primary key, it is a much faster select than choosing a random line from the database.

Jmz · 06-10-2008, 08:13 PM

Thanks for the answers, I still have a few questions about how the query is actually executed.

Say I have a query that says:

Quote:

SELECT * FROM tbl_gallery WHERE fld_userid = $UserID ORDER BY RAND() LIMIT 4

I understand (from what I've read) that the database will shuffle the rows and select 4 at random, but will the WHERE clause be executed first? Would it pick the rows that meet the where criteria and then shuffle them and select the 4 or does it shuffle all the rows and select 4 and then check the where clause?

xenon · 06-10-2008, 10:01 PM

It will check the condition(s) first, and then pick up 4 from the remaining rows, of course.

buggabill · 06-11-2008, 03:18 PM

That is why it is cpu intensive. RAND() will execute every time the WHERE clause does which will once for every record that would be returned regardless of the LIMIT.

From what I gather/understand/whatever, RAND will assign a random number to all the rows that fit the criteria and then order the results by those numbers.

The full query still needs to run even though you have the LIMIT in there, because you are ordering the results. Only the top 4 will be returned.