Get Random Row with MySQL Without ORDER BY RAND()
This is an update to a previous post of mine which uses the RAND() method. Using the following code, you can retrieve a random row much, much faster (MySQL 4.1.x/5.0.x), with thanks to Jan Kneschke:
SELECT Replace: I've tested this with a table with over 660,000 records, and got a response in 0.0200 seconds, whereas with ORDER BY RAND() i got a response in 2.1599 seconds. AS r1
JOIN (SELECT ROUND(
RAND( ) * (
SELECT MAX( id ) FROM )
) AS id
) AS r2
WHERE r1.id >= r2.id
ORDER BY r1.id ASC
LIMIT 1;
Total Number of Rows:
Old Method:
New Method:
You Might Also Like...
14 Responses to “Get Random Row with MySQL Without ORDER BY RAND()”
Leave a Reply
Are each of these queries from a cold start of MySQL?
Yes, the server was restarted before each of the queries were executed.
Hi Ed,
This is a much better solution.
It is worth noting, however, that it has a significant caveat: it assumes that you don’t have any significant gaps in your IDs. If you have any gap (even of 1 record) the probability of returning the record following it is anywhere from a little, to substantially higher.
In this model, the probability of returning any given record is:
1 + [preceeding gap] / [max id]
whereas, the optimal probability to return any given record is:
1 / [record count]
Regards,
Jeremy
ummm i thought this was supposed to NOT use the rand() function? lol i see it in there still.
Haha, you’re right. I will change the title accordingly. It should say ‘Without ORDER BY Rand()’.
Thanks!
Nice info, keep up the good work
problem if your id column is missing some values
Hi Amol,
This example does assume that you’re using the ID row without any missing values. An ID row is usually set to auto increment, and as such will never have missing values.
Thanks!
Hi Ed,
Yes it true that an ID row is usually set to auto increment but we cannot guarantee that it won’t have missing values due to the fact that some admins might need to delete some rows and auto increment doesn’t go back and fill numbers into those missing rows.
I have one more query, if you could help I would be very glad. My query is that how can we retrieve a random row different for each day, like in “quote of the day”, “download of the day”, etc.
Thanks and Regards,
Amol
Apparently nobody, including Ed, realizes what the r1.id >= r2.id test does in this query. Because it’s equal to OR GREATER, if r2.id returns an id that is empty (deleted) in r1, that’s ok b/c of the greater than clause, which will return the next greatest id in r1 (limit 1).
[...] … its slightly enough and as your table grows in size, it gets slower and slower. Try this: http://edrackham.com/featured/get-ra…order-by-rand/ There are other suggestions out there too, search for "mysql don’t use random()". — [...]
Great tutorial. I’m concerned about query speeds because my site relies heavily on database usage.
david: set up a table with id’s like this
1
2
3
500000
and see how many runs it takes to find one of the first 3 rows
you *need* to have the id’s (or other sequence column) sequential for this solution. Look into a AFTER DELETE trigger to re-sequence, if you must delete some rows.
if i need 9 records
max id = 100
and round rand return 95
i will got only 5 records
so i make this one
SELECT `id`,`a`,`b`,`c`,`d` FROM xxxxxxxxxx AS data JOIN (SELECT ROUND(RAND() * ((SELECT MAX(`id`) FROM xxxxxxxxxx) – 9)) AS rid) AS rand WHERE data.id >= rand.rid ORDER BY data.id ASC LIMIT 9