Re: [PHP] How to optimize select of random record in DB ?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




What about in MySQL:

SELECT personID from persons ORDER BY RAND(NOW()) LIMIT 1

If the table is large, then the following might be better:

LOCK TABLES foo READ;
SELECT FLOOR(RAND() * COUNT(*)) AS rand_row FROM foo;
SELECT * FROM foo LIMIT $rand_row, 1;
UNLOCK TABLES;

There's a whole discussion on it in the MySQL documentation web site:

http://dev.mysql.com/doc/mysql/en/SELECT.html


On 15 Oct 2004, at 07:41, -{ Rene Brehmer }- wrote:

I made this code to pick a random record from a changeable number of records in a given table.
I'm just curious if any of the more awake coders out there can see a way to optimize this for better performance, since there's several other DB queries on the same page.


$records = mysql_query("SELECT COUNT(*) AS count FROM persons") or die('Unable to get record count<br>'.mysql_error());
$totalcount = mysql_result($records,0) - 1;
$rndrecord = rand(0,$totalcount);
$personquery = mysql_query("SELECT personID FROM persons LIMIT $rndrecord,1") or die('Unable to get random record<br>'.mysql_error());
$personID = mysql_result($personquery,0);
--
Rene Brehmer
aka Metalbunny


If your life was a dream, would you wake up from a nightmare, dripping of sweat, hoping it was over? Or would you wake up happy and pleased, ready to take on the day with a smile?

http://metalbunny.net/
References, tools, and other useful stuff...
Check out the new Metalbunny forums at http://forums.metalbunny.net/

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux