PHP, MySQL, and Other Web Snippets!
Featured
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 <COLUMN> FROM <TABLE> AS r1
JOIN (SELECT ROUND(
RAND( ) * (
SELECT MAX( id ) FROM <TABLE>)
) AS id
) AS r2
WHERE r1.id >= r2.id
ORDER BY r1.id ASC
LIMIT 1;
JOIN (SELECT ROUND(
RAND( ) * (
SELECT MAX( id ) FROM <TABLE>)
) AS id
) AS r2
WHERE r1.id >= r2.id
ORDER BY r1.id ASC
LIMIT 1;
Replace:
- <COLUMN> with the name of the column(s) you wish to retrieve
- <TABLE> with the name of the table you wish to retrieve the data from
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.
Total Number of Rows:
Old Method:
New Method:
Discussion
9 comments for “Get Random Row with MySQL Without ORDER BY RAND()”
Post a comment
Recent Comments
- jqlkvaf on Beginner's Mod_Rewrite Tutorial
- bkpszct on Beginner's Mod_Rewrite Tutorial
- bgfmbsz on Beginner's Mod_Rewrite Tutorial
- qxkjcfe on Beginner's Mod_Rewrite Tutorial
- Raimis on Beginner's Mod_Rewrite Tutorial
Most Emailed
- Simple Web 2.0 Design and CSS Tutorial - 1 emails
© 2007-2008 edrackham. 
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