Is using, ORDER BY RAND() bad?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

Is using, ORDER BY RAND() bad?

Post 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!
nickvd
DevNet Resident
Posts: 1027
Joined: Thu Mar 10, 2005 5:27 pm
Location: Southern Ontario
Contact:

Post by nickvd »

randomize it with php?
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Is using, ORDER BY RAND() bad?

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

If memory serves, no.. but it does have to read the entire result set. This is how ORDER BY works.
User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

Post 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.
wildwobby
Forum Commoner
Posts: 66
Joined: Sat Jul 01, 2006 8:35 pm

Post by wildwobby »

Anyone wanna benchmark it?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post 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...
(#10850)
User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

Post 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.
User avatar
jolinar
Forum Commoner
Posts: 61
Joined: Tue May 24, 2005 4:24 pm
Location: in front of computer

Post 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)
Post Reply