Randomizing MySQL Query Results

Mar 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:52 PM on Jul 30, 2009
Hi Thanks for the information.It is really a very nice function Regards Suman


3:25 PM on Jul 30, 2009
Since I wrote this article, I've learned some additional information about the ORDER BY rand() construct. See the addendum in the article above.

Leave a Comment

Ignore this field:
Never displayed
Leave this blank:
Optional; will not be indexed
Ignore this field:
Both Markdown and a limited set of HTML tags are supported
Leave this empty: