Jump to content
chiller

MYSQL Selecting random records

Recommended Posts

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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>';

?>

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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. 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...