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

skip the two step process and use RAND in the sql statement SELECT * FROM table1, table2 WHERE a=b AND c<d ORDER BY RAND() LIMIT 1000; bastien

From: -{ Rene Brehmer }- <metalbunny@metalbunny.net> To: php-db@lists.php.net,php-general@lists.php.net Subject: [PHP-DB] How to optimize select of random record in DB ? Date: Fri, 15 Oct 2004 07:41:44 +0200 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 Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php