MySQL randomly sort query result

How do you choose records randomly from MySQL database. Normally we will select all the related data from database and then store it to a buffer array. From the array, we randomly choose the records that we want.

There is a simpler way. Let say we have the following query which return 100 rows of record and we want to randomly choose 5 rows from it:

select column1, column2 from table1 where condition1 and condition2

We can simply tweak the query to return 5 rows randomly.

select column1, column2, rand () as randomColumn from table1 where condition1 and condition2 order by randomColumn limit 5

That’s it! We simply add a column with random values and the sorted by the randomColumn. This method is pretty useful for application such as Rotating Ads, Random Links/Post and etc.

Note: Do not use this method on query that return large amount of rows (e.g. more than 1 millions rows), it will slow down the overall execution time.