Fetching Random record(s) in MySQL DB

  March 11th, 2008 | In Programming | No Comments »

Its pretty common to fetch rows in a mysql result by using the ORDER BY command, but sometimes need arises when we have to fetch rows randomly.

There are many ways to fetch rows in a mysql database randomly..

1) Use php and find the total records using mysql_num_rows(), and using rand() function fetch the random rowid within the total range..
Example :

= $random_rowid";
  $random_record = mysql_query( $random_record_query );
?>

2) The other way of doing it is by simply using this SQL…

SELECT *
FROM mytable
ORDER BY rand()

This took 0.4 seconds on a 50,000 record table for me.
This works out in most cases, that if we do not have many rows ( like < 1000 ).

But when we have a situation where we need to fetch a random row from say a table with say 50,000 ( >10,000 )records then the above method will take horrendously large amount of time.

3) The alternative and the best way to retreive random rows without hurting your server is to use a table join

SELECT *
FROM MyTable T
JOIN (
 SELECT FLOOR( MAX( ID ) * RAND() ) AS Rand_ID FROM Table
) AS x
ON T.ID >= x.Rand_ID
LIMIT 1;
--ID is your primary column with a unique record number or something..

This took 0.001 seconds on 50,000 record table

The stuff in this tutorial is what i have learnt, when i was trying to fetch random rows for my sudoku site

  Tags : mysql, random records