Tuesday, February 1, 2011

Selecting random record from MySQL database table.

Solution 1 [SQL]
SELECT * FROM `table` ORDER BY RAND() LIMIT 0,1;
But it is slow because mysql create a temporary table with all the result rows and assigns each one of them a random sorting index.

Solution 3 [PHP]
$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 " );
it is best option

No comments:

Post a Comment