The begining of this story is a PHPNuke module. The module has a block that picks a random item from a database table and displays it. The content of the table never changes. Here is how the programmer of the block does it:
He knows that there are 6899 rows because the last id in the table has a value of 6899. So with the "rand" PHP function, he picks a random number from 1 to 6899 and then queries the database table with a WHERE clause that checks if the value of the id is equal to the picked number:
srand(time());
$random = (rand(1,6899));
$res = mysql_query ("select * from my_table where id=$random");
The problem here is that because of some deleted rows (he has forgotten he deleted them) in the table, the id is not a continuous sequence from 1 to 6899 and that's why some times the picked number (as a value of the id) not exists in the table and the PHPNuke block displays an empty box.
The RAND function in MySQL is precisely there to prevent this kind of problem. Instead of picking a random id with a PHP code, let the server do it for you:
SELECT * FROM my_table ORDER BY RAND() LIMIT 1;
This query returns a random row from the table my_table, no matter how many rows there are and no matter if the id is a continuous sequence of numbers or not. With this query you are sure you get a random row every single time.
Conclusion:
Never reinvent the wheel.
He knows that there are 6899 rows because the last id in the table has a value of 6899. So with the "rand" PHP function, he picks a random number from 1 to 6899 and then queries the database table with a WHERE clause that checks if the value of the id is equal to the picked number:
srand(time());
$random = (rand(1,6899));
$res = mysql_query ("select * from my_table where id=$random");
The problem here is that because of some deleted rows (he has forgotten he deleted them) in the table, the id is not a continuous sequence from 1 to 6899 and that's why some times the picked number (as a value of the id) not exists in the table and the PHPNuke block displays an empty box.
The RAND function in MySQL is precisely there to prevent this kind of problem. Instead of picking a random id with a PHP code, let the server do it for you:
SELECT * FROM my_table ORDER BY RAND() LIMIT 1;
This query returns a random row from the table my_table, no matter how many rows there are and no matter if the id is a continuous sequence of numbers or not. With this query you are sure you get a random row every single time.
Conclusion:
Never reinvent the wheel.
08 Jun 2006 18:44:02
But RAND() on a large dataset turns your database to mud. This is not a good solution in production.
I haven't found a good solution where I need more than one row on two joined tables but here's a great solution if you have one table and need ONE row returned:
http://jan.kneschke.de/proj...
28 Mar 2007 05:25:11
eon is telling the truth. Just tried it on my database with 3 million + rows. Didn't work very well.
04 Jul 2007 09:07:48
Very slow
18 Jul 2007 13:58:56
What about this:
First, "SELECT COUNT(*) FROM my_table", save the result as a variable $count, then $rand_nr = rand($count), and finally
"SELECT * FROM my_table LIMIT '" . $rand_nr . "', 1"
25 Jul 2007 09:46:52
i think we should do it case by case, if your database is small, mustapha code can be implemented. otherwise use PHP random code.
06 Oct 2007 05:59:41
I want to update a field with random values of rang 0 to 1000. How i can do this?
20 Mar 2008 02:15:16
A small tricky way how to skip deleted rows came into my mind...
$random = rand(1,($rows_count - 1));
$result = mysql_query("select * from my_table where id > $random limit 1");
It may be buggy, and it will when you have deleted for example 10 last rows and rand will choose some high number before the max. :((
11 Jun 2008 15:58:36
If you have data with IDs (preferably PK, or otherwise indexed) and either care so much about randomness in the face of holes in the continuity of those IDs (or assume holes in ids are randomly distributed), you can pull a dirty trick:
First SELECT RAND()*(SELECT max(id) from mytable), then hand that value back in in a query like: SELECT * from mytable where id>=myrandid LIMIT 1
The >= means holes don't really matter, it just uses the next one.
25 Jun 2008 06:31:49
thanks for this info - exactly what I need.
I'm only searching through a small number of results, so this should work fine.
27 Jul 2008 15:18:31
thanx, now i get it method ... it's faster than other !
26 Aug 2008 16:28:43
THANX!
http://www.pokerfree.nl
05 Sep 2008 01:14:27
use LIMIT when using RAND() on large databases.
or SELECT * and then use arry_rand($res, 10); function.
08 Oct 2008 00:16:32
They did not want to reinvent the wheel, RAND () is very, very slow!
There are other efficient solutions.
See this article:
http://akinas.com/pages/en/...
14 Oct 2008 17:07:43
ORDER BY RAND() is also sweet feature
03 Dec 2008 14:05:15
Great tip, thanks!
20 Dec 2008 12:13:57
besure to post an alternate method when you found one.
24 Dec 2008 18:44:48
how can i use rand() for fetching more than 1 random records from table?
08 Feb 2009 21:32:58
Dude lol, NEVER use ORDER BY RAND()!
The reason the programmer did what he did, is because he knew exactly how painfully slow ORDER BY RAND() is.
And as far as I know, LIMIT won't improve anything if you do ORDER BY RAND(), because MySQL first creates a temporary table with all the data, then orders it, and then applies the limit to the selection.
Unfortunately there doesn't seem to exist a 1 solution fits all for this.
16 Apr 2009 11:47:48
MySQL is improving the ORDER BY RAND() soon, they just don't have it as a priority.
It was in one of their email notices a couple of months ago, not in front of my office computer to find it at the moment.
26 Jun 2009 22:51:30
$lastmonth = mktime(0, 0, 0, date("m")-1);
SELECT some_content FROM some_table
WHERE some_content_added >= $lastmonth
ORDER BY RAND()
LIMIT 4
some_content_added, is a time stamp inserted at the time of record creation with the time() function.
This retrieves 4 rows, created within the last month at random. Providing you don't add a silly amount of rows within the space of a month, then this should still produce a fast result, should it not?
I haven't tried it on a live site, but it appears to work just fine on my production server.