Page 1 of 1
Is using, ORDER BY RAND() bad?
Posted: Sat Feb 03, 2007 11:21 am
by seodevhead
I have a php page that outputs all sorts of posts made by users, and I wish to have the posts' order to be random each time the page is accessed. However, the only way I know how to do this is by using something like the following:
Code: Select all
SELECT post FROM posts_table ORDER BY RAND()
However, I remember reading somewhere that using ORDER BY RAND() is not a good idea because it puts a tremendous strain on the server? Is this true? The result set that would be returned by such a query would only contain probably 100 or so records at most. Is there another way to randomly output the data? Thanks for any help and guidance!
Posted: Sat Feb 03, 2007 11:49 am
by nickvd
randomize it with php?
Re: Is using, ORDER BY RAND() bad?
Posted: Sat Feb 03, 2007 12:18 pm
by califdon
I've never tried it, but at first glance, I don't think that would work anyway, since ORDER BY normally refers to columns of a table.
My suggestion, especially if you're only anticipating a hundred or so rows, would be to load an array, then scramble it, as nickvd suggested, with a php algorithm, then output it.
Posted: Sat Feb 03, 2007 12:41 pm
by feyd
Ordering by RAND() does actually work, however it requires reading the entire table so if it's queried often, it's probably not the best, performance-wise.
Posted: Sat Feb 03, 2007 12:56 pm
by seodevhead
feyd wrote:Ordering by RAND() does actually work, however it requires reading the entire table so if it's queried often, it's probably not the best, performance-wise.
Hey feyd... it has to read the whole table even if there are multiple WHERE conditionals that cut down on the number of records?
Posted: Sat Feb 03, 2007 1:24 pm
by feyd
If memory serves, no.. but it does have to read the entire result set. This is how ORDER BY works.
Posted: Sat Feb 03, 2007 1:34 pm
by AKA Panama Jack
feyd wrote:Ordering by RAND() does actually work, however it requires reading the entire table so if it's queried often, it's probably not the best, performance-wise.
Actually it doesn't read the entire table. It creates a result set ordered based upon the primary index without scanning the entire table if the WHERE clause is missing.
If you do have a WHERE clause it will create a random result set based upon the INDEXED fields in the WHERE clause without a table scan. If you have field in the WHERE clause that are not indexed then the RAND() will perform a table scan just like any other query that uses non-indexed fields in the WHERE clause.
In other words using an "ORDER BY RAND()" isn't any more of a database server load than using "ORDER BY myfield" as long as you have the WHERE clause fields indexed.
Posted: Sun Feb 04, 2007 2:34 pm
by wildwobby
Anyone wanna benchmark it?
Posted: Sun Feb 04, 2007 10:01 pm
by Christopher
You could do something like this:
Code: Select all
$rows = $model->find();
$order = range(0, count($rows)-1);
shuffle($order);
foreach ($order as $n) {
$view->show($rows[$n]);
}
Sort of old-school Fortran style, but...
Posted: Mon Feb 05, 2007 1:46 pm
by AKA Panama Jack
Ok, I have been doing some testing and what I have found is that the RAND() function itself is slow at creating random number sequences. If you use the RAND() anywhere in your query it can cause a slowdown. The more often the query has to use the RAND() internally the more it will slow down the query.
When you are using "ORDER BY RAND()" the ORDER BY is still going to use the indexing for speed but the RAND() function will be creating random numbering to represent each indexed record. Creating the random sequencing is very slow.
If you have a query similar to
Code: Select all
SELECT id FROM mytable WHERE id=FLOOR(10000 * RAND())
where you have a table with 10000 known sequential records and want a random record. This is incredibly SLOW. It is about 1000 times slower than...
Code: Select all
SELECT id FROM mytable WHERE id=FLOOR(10000 * 0.5)
The fastest method would be to use two queries. I am going to use
ADOdb Lite/ADOdb code for the example because I can write it faster.
Code: Select all
// get all record ids from the table
$id_array = array();
$query = $db->Execute ("SELECT id FROM mytable" );
while (!$query->EOF)
{
$id_array[] = $query->fields['id'];
$query->MoveNext();
}
// now randomize array keys
shuffle($id_array);
// If you need a group of items just grab the first few. In this example we will use 10 Build a list of 10 record ids
$list = "";
$count = 10;
for($i = 0; $i < $count; $i++)
{
$list .= $id_array[$i];
if($i < $count - 1)
{
$list .= ", ";
}
}
// grab the 10 records
$result_array = array();
$query = $db->Execute ("SELECT * FROM mytable WHERE id IN ($list)" );
while (!$query->EOF)
{
$result_array[] = $query->fields;
$query->MoveNext();
}
// randomize the order of the returned records
shuffle($result_array);
Believe it or not this is many, many, MANY times faster than using RAND() inside a query.
If you just need one record and this is still faster than using RAND().
Code: Select all
// get all record ids from the table
$id_array = array();
$query = $db->Execute ("SELECT id FROM mytable" );
while (!$query->EOF)
{
$id_array[] = $query->fields['id'];
$query->MoveNext();
}
// now randomize array keys
shuffle($id_array);
// grab the one record
$result = array();
$query = $db->Execute ("SELECT * FROM mytable WHERE id = " . $id_array[0] );
if (!$query->EOF)
{
$result_array = $query->fields;
}
The MySql random number generator is quite frankly infernally slow. You are far better off using native PHP code to create random record lists.
Posted: Wed Feb 07, 2007 4:43 pm
by jolinar
I'd suggest doing something along these lines:
Code: Select all
$random = rand();
$random segment = mysql_result($resultset,$random%mysql_numrows($resultset),"fieldname");
applied to all fields (or be lazy like me and create a routine to get everything from a for and bung it in an associative array)