Sunday, October 19, 2008

Selecting random record from MySQL database table.

Delicious 0
There are so many solution to select random record from MySQL table by using rand(). But we should have to know what solution is the simple and best executing. I found this post from Akinas Blog.

Solution 1 [SQL]: The slowest method is solution 1. Let's say that it took 100% of time to execute.


Solution 2 [using with PHP]: took 79%.
$range_result = mysql_query( " SELECT MAX(`id`) AS max_id , MIN(`id`) AS min_id FROM `table` ");
$range_row = mysql_fetch_object( $range_result );
$random = mt_rand( $range_row->min_id , $range_row->max_id );
$result = mysql_query( " SELECT * FROM `table` WHERE `id` >= $random LIMIT 0,1 "); 

Solution 3 [using with PHP]: took 13%.
$offset_result = mysql_query( " SELECT FLOOR(RAND() * COUNT(*)) AS `offset` FROM `table` ");
$offset_row = mysql_fetch_object( $offset_result );
$offset = $offset_row->offset;
$result = mysql_query( " SELECT * FROM `table` LIMIT $offset, 1 " ); 

Solution 4 [SQL]; took 16%
SELECT * FROM `table` WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `table` ) ORDER BY id LIMIT 1;

You can see what different between these solutions from Akinas Blog.

Related Posts:

Dont just read, download and go, a thank will be helpful for the author to keep going a good job.

Share this post to tell other people about its

blog comments powered by Disqus