Randomizing MySQL Query Results

Published on March 18, 2009

When I added the favorite photos feature to my photo album software, I wanted a way to randomly show a subset of said favorites on the albums display page. I initially thought about implementing my own means of doing this through PHP. Ultimately, I wanted random selection without replacement, so that viewers would not see multiple copies of the same image in the ‘Favorites Preview’ section. Thankfully, MySQL saved the day!

When sorting a MySQL query, you can opt to sort randomly:

SELECT {some columns} FROM {some tables}
WHERE {some condition} ORDER BY rand()

The rand() function in PHP essentially gives you random selection without replacement for free! How great is that? It was an easy solution to a not-so-simple problem, and saved me a lot of programming time.

Update: I have since learned that the ORDER BY rand() call is horribly inefficient for large data sets. As such, it should ideally be avoided. There’s a great article describing ways to work around these performance limitations.

2 Comments

Suman

Hi

Thanks for the information.It is really a very nice function

Regards
Suman

Jonah

Since I wrote this article, I’ve learned some additional information about the ORDER BY rand() construct. See the addendum in the article above.

Comments are closed.

Copyright © 2004-2018 Jonah Bishop. Hosted by DreamHost.