I have come across more than one instance when I had to select a random record from a table in a MySQL database. Here is how to do it.
The simple but slow method
SELECT * FROM mytable ORDER BY RAND() LIMIT 1;
Although simple, the above query can be very slow on tables which have a large number of records. This happens because MySQL makes a temporary table with a random number assigned to each row. It then sorts the table and returns the one record from the sorted list.
Random record selection by id generation : A better and faster method
The table will need to have an ‘id’ field which is an auto-incrementing integer. The approach is to generate a random number which falls in the range of the values of the ‘id’ field. MySQL MAX() and MIN() grouping functions allow you to choose the maximum and minimum values of a field.
The python approach
Sample python code to do the random record selection
import random ... cursor.execute('SELECT MIN(id) FROM mytable') min_value = cursor.fetchone() cursor.execute('SELECT MAX(id) FROM mytable') max_value = cursor.fetchone() random_id = random.randint(min_value, max_value) cursor.execute('SELECT * FROM mytable WHERE id = %s', (random_id,)) random_record = cursor.fetchone()
Pure SQL approach
MySQL offers a RAND() function which generates a random floating point number between 0.0 and 1.0. We will use this value and scale it to fit into the max – min value range of the id field.
SELECT * FROM mytable WHERE id = (SELECT MIN(id) + FLOOR((MAX(id)+1) * RAND()) FROM mytable ) LIMIT 1;
So next time you have to select a random record, you know what to do