chiller Posted February 25, 2012 Posted February 25, 2012 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? Quote
Administrators Nathan Posted February 25, 2012 Administrators Posted February 25, 2012 Hmmm...what are you trying to accomplish? Why do you want to select random rows? Quote
bryce12 Posted February 27, 2012 Posted February 27, 2012 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. Quote
Administrators Tony Posted February 27, 2012 Administrators Posted February 27, 2012 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? Quote
haye55987 Posted February 28, 2012 Posted February 28, 2012 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 Quote
rapt0r Posted March 1, 2012 Posted March 1, 2012 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>'; ?> Quote
Siddharth Ramakrishnan Posted March 9, 2012 Posted March 9, 2012 If your problem doesnt solve yet! Feel free to pm me. I can help you! Quote
sam300 Posted April 18, 2012 Posted April 18, 2012 But what is the point of this? Can you explain? Quote
enim Posted December 16, 2013 Posted December 16, 2013 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. Quote
crazycroc Posted January 9, 2014 Posted January 9, 2014 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. Quote
Kaushik Posted December 24, 2018 Posted December 24, 2018 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. Quote
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.