Selecting random record from MySQL database table.

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.

SELECT * FROM `table` ORDER BY RAND() LIMIT 0,1;

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.

Posted in: Tutorials
Tags: MySQL