Select Random Records in Database Table

In this tip, you will learn various techniques to select random items from a database table in MySQL.
MySQL does not have any automatic way to select random items from a database table. In some programming tasks, it is required and very useful to select random items from a result set such as:

• You'll need to select a random picture to display it for a category, so each time when the visitor come to your website, they will see different pictures displaying on the categories.
• You'll need to select a random banner to display to visitors.
• You'll need to pick a row in "quote of the day" application.
• And more and more cases you can think of...

In order to achieve this result, MySQL allows to do that via RAND function. To select a random value from a result set you perform the following query:

SELECT * FROM table  ORDER BY RAND()  LIMIT 1 

The key technique used above is randomizing the returned records and then pick the first one. If you want to select n random items just change the parameter after LIMIT as follows:

SELECT * FROM table  ORDER BY RAND()  LIMIT n

The technique as demonstrated above work very well with the small table. With the table which has many records, it could be very slow because we have to sort the entire table to pick random items. To work around this problem, we will use another technique as demonstrated below:

1. First we select a random ID(s) of a column. This column should be the primary key and the value is in sequential range.
2. Then pick the rows based on the ID(s) we selected

The SQL script for doing this is as follows:

SET @ID = FLOOR(RAND( )* N) + 1; 
 SELECT *  FROM table  WHERE ID >= @ID LIMIT 1  

This technique work faster because the ID column is indexed and we don't have to order the whole table as previous one.