Objective: In this tutorial, discuss various techniques to select random records from a database table in MySQL.
There is no automatic way to select random items from a mysql database table. 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:
| 1 | 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 that is:
- First we select a random ID(s) of a column. This column should be the primary key and the value is in sequential range.
- Then pick the rows based on the ID(s) we selected
The SQL script for doing this is as follows:
| 1 | SET @ID = FLOOR(RAND( )* N) + 1; | |
| 2 | 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.
