Return Random Records in MySQL
The ability to return random records from a MySQL table is invaluable. Returning random records is helpful when:
- featuring items without showing favoritism to one
- testing different result sets in your PHP
- looking to display specific items in a non-specific order
The great part about selecting random records from your MySQL tables is that it's really easy.
The Code
SELECT product_id, title, description FROM products WHERE active = 1 AND stock > 0 ORDER BY RAND() LIMIT 4
Comments
Be Heard!
Share your thoughts without being a jerk! And wrap your code in <code> tags, f00!
You are just a random-useful-tip generating MACHINE =). This one will be a useful reference especially, just had a reader ask about this recently.
Thanks Chris!
I doubt this is a good idea. Try it on a really big table (like the example products table suggests) and you will soon notice the performace problems. The query not only requires to examine all rows (full table scan) but also to sort them.
You can also use the “explain” keyword to see what MySQL does with your query.
For a really big table, it would probably be best to:
1. Make a query to the database to obtain all ids.
2. Place them into one array.
3. Randomly select (using PHP) {x} number of id’s from the array.
4. Build a string that will be used in another query, e.g. “WHERE id = 2 OR id = 32847 OR id = 94384 OR…”
5. Execute the query, grab information.
If you’re worried about a table scan, rather than fetch every ID into PHP for another round trip, surely it would be better to use a subquery…
SELECT *
FROM products
WHERE id IN(
SELECT id
FROM products
ORDER BY RAND()
LIMIT 4
)
If id is indexed I’m pretty sure it just has to read the index.
You’re correct Steve.
A colleague challenged me to find a way to ORDER the records by RAND() and then by title — that’s why my mind was in PHP mode.
Awesome contribution — thank you!
If id is indexed and you sort by RAND() that won’t help you too much.
One solution I could think is to do a
SELECT COUNT(*) FROM products
generate a random number between 0 and the number of rows and use
SELECT * FROM products LIMIT 1 OFFSET $ramdom_number
If id is indexed, I’m pretty sure reading just the index is faster than doing a table scan. The actual indexing won’t help, but the fact that all the data you need is held in the index does.
http://www.carlj.ca/2007/12/16/selecting-random-records-with-sql/
A similar article for all sql systems..
I actually confused these two posts today as I had posted a comment on the other Post and didn’t see that here…
Do check my Comment there…
RAND(), RAND(N)
Returns a random floating-point value v in the range 0 SELECT RAND();
-> 0.9233482386203
mysql> SELECT RAND(20);
-> 0.15888261251047
mysql> SELECT RAND(20);
-> 0.15888261251047
mysql> SELECT RAND();
-> 0.63553050033332
mysql> SELECT RAND();
-> 0.70100469486881
mysql> SELECT RAND(20);
-> 0.15888261251047
The effect of using a non-constant argument is undefined. As of MySQL 5.0.13, non-constant arguments are disallowed.
To obtain a random integer R in the range i SELECT * FROM tbl_name ORDER BY RAND();
ORDER BY RAND() combined with LIMIT is useful for selecting a random sample from a set of rows:
mysql> SELECT * FROM table1, table2 WHERE a=b AND c ORDER BY RAND() LIMIT 1000;
Note that RAND() in a WHERE clause is re-evaluated every time the WHERE is executed.
RAND() is not meant to be a perfect random generator, but instead is a fast way to generate ad hoc random numbers which is portable between platforms for the same MySQL version.
From http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_rand
I tested this myself, and posted my results here:
http://www.seven.net.nz/2007/random-results-mysql/
Steve: Your MAX(id) idea is pretty good but not flawless — what if an ID has been deleted? Your query could return less rows than desired.
Great hidden trick. How about a post that explains how to return multiple results from MySql Stored procedure? I know they’ve added this from 5.0+ or 4.0+, not sure. But have not really seen any examples around.
Thanks!
Hello,
I would still caution against the order by rand(). Even if its not doing a table scan, it can still be harmful.
I went into detail about how I optimized my own issue with this (I had a scenario where I had to choose a random user…) in this blog post:
http://www.102degrees.com/blog/2007/06/27/random-user-generation-optimized/
For those who don’t want to read all of the posting, here’s what I came up with (of course, optimized for my situation):
$sql = “select u.userID, u.userName, s.globalPasswordProtect, s.allowRandom
from Tuser u JOIN
(select (rand() *
(select max(userID)
from Tuser)) as id)
AS r2
inner join TuserSettings s
on u.userID=s.userID
where u.userID >= r2.id and
s.allowRandom=1 and s.globalPasswordProtect=0 and u.status=’A’
order by u.userID ASC
limit 1″;
Hi, im quite new in PHP and im currently developing a website for online exam in which i would like to generate set of questions at random which is retrieved from the database do you think the use of order by rand() is efficient to use…. hope somebody could help me with these thanks in advance!
These method is nice, easier then trying to do random sorting using php. I’d use this to randomize tags like they do on tagtill.com
I gave the thing a few minutes of though and came up with the following hybrid of counting rows and protecting against deleted ID’s.
Note that the db() function return a instance of the database object derived from the AdoDB abstraction layer base. Some docs:
http://phplens.com/adodb/reference.functions.getone.html
http://phplens.com/adodb/reference.functions.getrow.html
But this can be changed to any DB philosophy.
while ($randomRecord === FALSE)
{
$randomRecord = db()->GetRow(“SELECT * FROM quotes WHERE id = ” . rand(0,$tableCount-1);
if (count($randomRecord) === 0)
$randomRecord = FALSE;
}
Anyone see any holes?
Hmm posted bad code – here’s the version it should have been:
$randomRecord = FALSE;
$tableCount = db()->GetOne(“SELECT COUNT(id) FROM quotes”);
while ($randomRecord === FALSE)
{
$randomRecord = db()->GetRow(“SELECT * FROM quotes WHERE id = ” . rand(0,$tableCount-1);
if (count($randomRecord) === 0)
$randomRecord = FALSE;
}
I do not think this is a good idea. If you have 100 records created an the deleated the first 98, your remaining ids are 98 and 99. Your count is 2 and your random numbers 0 and 1…
fetching the maximum index is the better way:
$tableCount = db()->GetOne(“SELECT MAX(id) FROM quotes”);
“The great part about selecting random records from your MySQL tables is that it’s really easy.”
The bad part about it is that it’s really wrong ;-)
Why would it be wrong to select random records?
For instance we have a page on our website where we want to display 4 random floor plans from our library of over 1200 floor plans. Pretty much any “featured products” website section is likely random driven in some manner.
@Newerton: Care to share why?
Hi all just come across this discusion and was wondering about how to select a small randomly generated sub set of rows from a table.
Since there seems to be performance problems with order by rand() the most solutions seems to do a sub set of rows from a generated start value i.e
select * from test_table where id >= “rand_nr” Limit 10
this will how ever only give you a sub set of ten rows starting from id greater then or equal to the random number generated.
Does anyone have a solution where all ten (or any number greater then 1) rows are randomly selected??
i.e gives id 25, 705, 1003, 2, 2306 … and so on.
Thanks in advance. /Tord
For everyday end user stuff chances are RAND() will be perfectly fine. If you’re working with large tables then not so much. So most people reading this site I think would benefit using RAND(). How many people are really working on sites with large tables?
At my job (social networking site) the RAND() isn’t an option, so the randomization is done in PHP.
For example say we want 5 random videos out of the most recent videos. What I setup was grabbing the latest 1000 videos and store them into an array, which is Memcached for ~4 hours through a cron job. Then frontend just do a shuffle on the array and grab the first five rows.
So people worrying about large tables, chances are they’ll have caches setup anyway.
P.S. some of your CSS suggestions started making me wonder. Also for my next personal project might give MooTools a whirl. Request.JSON intrigues me.
Ah you saved me a lot of time !
ANOTHER VERY useful tip from you, thanks a lot :)
I am also fencing this problem:
i want fetch data from database in descending order with randomly
so can’t get result
Parvez, resort what you grab:
SELECT t.* FROM (
SELECT id,firstname,lastname
FROM your_table
ORDER BY RAND()
LIMIT 5
) as t
ORDER BY t.firstname DESC
Fantastic! What a tight piece of code! Thanks
IT can also be done by ‘order by random_field_name’ on table
We just have to pass random fields in query.
how i can select random number of rows from a table without rand() on mysql??
How about this for a single random record?
select * from `table` where id>=(select RAND()*max(id) from `table`) limit 0,1
It isn’t completely fair since a deleted record gives the previous id twice the chance of being selected.
This page:
http://stackoverflow.com/questions/1823306/mysql-alternatives-to-order-by-rand has a really good solution in case you need more complex
WHEREclauses.It involves a subquery generating a few (as few as the number of returned rows). Check it out!