HOBALL 筆記簿

July 15, 2009

MySQL selecting a random row efficiently

Filed under: SQL — Tags: , — hoball @ 10:49 pm

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 Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: