Randomize MySQL results the right way?

I wanted to get some randomized results but read many warnings about the dangerous inefficiency of using MySQL’s “ORDER BY RAND()” functionality. After searching around, I came across this solution which seems very good! Copied without permission for posterity.

Often around the internet I find people asking how to get random rows out of MySQL. Typically I see helpful people giving this:
SELECT * FROM table ORDER BY RAND();

However what a lot of people don’t realise, while ok for systems will small tables and limited accessed, this as a huge performance hit when used like this.
The correct way to order your results by a random number is:
SELECT *, RAND() as rand FROM table ORDER BY rand;

Why? Well the answer is actually very simple, ORDER BY RAND() causes the order to be recalculated every time MySQL fetches a new row as for each row RAND() returns a different value. When used in the select the value is only calculated once per row and the results are only ordered once.

This entry was posted in Uncategorized. Bookmark the permalink.

1 Response to Randomize MySQL results the right way?

Leave a Reply

Your email address will not be published. Required fields are marked *