mysql Selecting random records

[chiller]

The simplest way of selecting random rows from the MySQL database is to use "ORDER BY RAND()" clause in the query.

SELECT * FROM `table` ORDER BY RAND() LIMIT 0,1;

 

The problem with this method is that it is very slow when your tables contain thousands of records.

 

 

I was wondering if you knew any workarounds to speed things up?

[Nathan]

Hmmm...what are you trying to accomplish?

 

Why do you want to select random rows?

[bryce12]

Yeah I imagine its going to consume a lot of CPU and memory. The best way to speed this is not to fetch random records at all LOL. See when you specify a filter, it usually speed things up since you are instructing the DB to fetch records that match a particular criteria. But here you are not specifying any filter and on top of that you are instructing the DB to fetch records at random. So the DB has to keep track of what records it has already sent as output and which ones are still left. Needless to say if the table has thousands or millions of rows, this operation can become quite resource intensive.

[Tony]

I would think that you could write a script to count the rows of data that you want to be selected randomly, update a table that is just an auto increment INT primary key that contains the same number of rows as your random data, then just randomly select a row from that table to give you the exact number for the row that you will grab from the table that contains the data

 

clear as mud?

[haye55987]

The simplest way of selecting random rows from the MySQL database is to use "ORDER BY RAND()" clause in the query.

SELECT * FROM `table` ORDER BY RAND() LIMIT 0,1;

 

The problem with this method is that it is very slow when your tables contain thousands of records.

 

 

I was wondering if you knew any workarounds to speed things up?

 

I think it really depends how "random" it has to be. There are other ways to randomize a set besides looking at the built in functions.

Example: Converting a date column into number 06/12/2012 --> 06122012 then doing a mod on that number or something to determine "randomness"

 

Give a little more on your situation and I"m sure there is a specific way to randomize your data set

[rapt0r]

I did some research and I have found a solution for your problem! You should try out the code below, it returns a random row in about 0.05 seconds:

<?php

 //CODE FROM WWW.GREGGDEV.COM

 function random_row($table, $column) {

  $max_sql = "SELECT max(" . $column . ")

			  AS max_id

			  FROM " . $table;

  $max_row = mysql_fetch_array(mysql_query($max_sql));

  $random_number = mt_rand(1, $max_row['max_id']);

  $random_sql = "SELECT * FROM " . $table . "

				 WHERE " . $column . " >= " . $random_number . "

				 ORDER BY " . $column . " ASC

				 LIMIT 1";

  $random_row = mysql_fetch_row(mysql_query($random_sql));

  if (!is_array($random_row)) {

	  $random_sql = "SELECT * FROM " . $table . "

					 WHERE " . $column . " < " . $random_number . "

					 ORDER BY " . $column . " DESC

					 LIMIT 1";

	  $random_row = mysql_fetch_row(mysql_query($random_sql));

  }

  return $random_row;

 }



 //USAGE

 echo '<pre>';

 print_r(random_row('YOUR_TABLE', 'YOUR_COLUMN'));

 echo '</pre>';

?>

[Siddharth Ramakrishnan]

If your problem doesnt solve yet! Feel free to pm me. I can help you!

[sam300]

But what is the point of this? Can you explain?

[enim]

But what is the point of this? Can you explain?

 

A lot of different things.  Perhaps you just want a randomly list of products to display on a page, for instance.  Or you need to do some data-checking on a huge table, and just want to spot check a few rows.  The uses are pretty endless.

[crazycroc]

I did some research and I have found a solution for your problem! You should try out the code below, it returns a random row in about 0.05 seconds:

<?php

  //CODE FROM WWW.GREGGDEV.COM

  function random_row($table, $column) {

	  $max_sql = "SELECT max(" . $column . ")

				  AS max_id

				  FROM " . $table;

	  $max_row = mysql_fetch_array(mysql_query($max_sql));

	  $random_number = mt_rand(1, $max_row['max_id']);

	  $random_sql = "SELECT * FROM " . $table . "

					 WHERE " . $column . " >= " . $random_number . "

					 ORDER BY " . $column . " ASC

					 LIMIT 1";

	  $random_row = mysql_fetch_row(mysql_query($random_sql));

	  if (!is_array($random_row)) {

		  $random_sql = "SELECT * FROM " . $table . "

						 WHERE " . $column . " < " . $random_number . "

						 ORDER BY " . $column . " DESC

						 LIMIT 1";

		  $random_row = mysql_fetch_row(mysql_query($random_sql));

	  }

	  return $random_row;

  }

 

  //USAGE

  echo '<pre>';

  print_r(random_row('YOUR_TABLE', 'YOUR_COLUMN'));

  echo '</pre>';

?>

 

This was what I was going to suggest, it works because you don't have to rely on mySQL to arrange the entire database (doesn't matter what the order is)and taking the first row. Instead you are simply generating a random number and then requesting that specific row number which is clearly much faster.

[Kaushik]

This query will run through the entire database and will take time and consume system memory. What are you using this selxrion set for? I do not see any purpose in selecting random data from a database. Perhaps you might find it useful in testing a sample.