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.