MySQL selecting a random row efficiently

Here are the homework I made today, for the quest for an efficient way to get a random row from a MySQL database:
source 1 : http://akinas.com/pages/en/blog/mysql_random_row/

$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 ” );

A reply by Pascal

Solution 4 took 1 minute on a table with 598420 rows.
I ended up doing this in PHP (table is static at this point)
$r = rand(1,598420);
$sql = “SELECT * FROM table WHERE id = $r”;
Takes 0.01 secs.

source 2 : http://www.dasprids.de/blog/2008/06/07/fetching-random-rows-of-mysql-efficiently

source 3 : http://poeticcode.wordpress.com/2007/12/31/mysql-select-random-row-very-efficient/

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s