quick selection of a random row from a large table

Question

What is a fast way to select a random row from a large mysql table?

I'm working in php, but I'm interested in any solution even if it's in another language.

Answer 1

With a order yo will do a full scan table. Its best if you do a select count(*) and later get a random row=rownum between 0 and the last registry

Answer 2

Use the below query to get the random row

SELECT user_firstname ,
COUNT(DISTINCT usr_fk_id) cnt
FROM userdetails 
GROUP BY usr_fk_id 
ORDER BY cnt ASC  
LIMIT 1

Answer 3

In pseudo code:

sql "select id from table"
store result in list
n = random(size of list)
sql "select * from table where id=" + list[n]

This assumes that id is a unique (primary) key.

Answer 4

Create a Function to do this most likely the best answer and most fastest answer here!

Pros - Works even with Gaps and extremely fast.

<?

$sqlConnect = mysqli_connect('localhost','username','password','database');

function rando($data,$find,$max = '0'){
   global $sqlConnect; // Set as mysqli connection variable, fetches variable outside of function set as GLOBAL
   if($data == 's1'){
     $query = mysqli_query($sqlConnect, "SELECT * FROM `yourtable` ORDER BY `id` DESC LIMIT {$find},1");

     $fetched_data = mysqli_fetch_assoc($query);
      if(mysqli_num_rows($fetched_data>0){
       return $fetch_$data;
      }else{
       rando('','',$max); // Start Over the results returned nothing
      }
   }else{
     if($max != '0'){
        $irand = rand(0,$max); 
        rando('s1',$irand,$max); // Start rando with new random ID to fetch
     }else{

        $query = mysqli_query($sqlConnect, "SELECT `id` FROM `yourtable` ORDER BY `id` DESC LIMIT 0,1");
        $fetched_data = mysqli_fetch_assoc($query);
        $max = $fetched_data['id'];
        $irand = rand(1,$max);
        rando('s1',$irand,$max); // Runs rando against the random ID we have selected if data exist will return
     }
   }
 }

 $your_data = rando(); // Returns listing data for a random entry as a ASSOC ARRAY
?>

Please keep in mind this code as not been tested but is a working concept to return random entries even with gaps.. As long as the gaps are not huge enough to cause a load time issue.

Answer 5

I see here a lot of solution. One or two seems ok but other solutions have some constraints. But the following solution will work for all situation

select a.* from random_data a, (select max(id)*rand() randid  from random_data) b
     where a.id >= b.randid limit 1;

Here, id, don't need to be sequential. It could be any primary key/unique/auto increment column. Please see the following Fastest way to select a random row from a big MySQL table

Thanks Zillur - www.techinfobest.com

Answer 6

I have used this and the job was done the reference from here

SELECT * FROM myTable WHERE RAND()<(SELECT ((30/COUNT(*))*10) FROM myTable) ORDER BY RAND() LIMIT 30;