I'm a programmer specialising in performant and scalable systems using PHP and Ruby and cooking


Published:
On MySQL

MySQL ORDER BY RAND()

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.