Index ¦ Archives ¦ Atom ¦ RSS

Selecting a random row from a table in mysql

database

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 :)

Additional information

© Prashanth Ellina. Built using Pelican. Theme by Giulio Fidente on github.