Randomly ordering data with MySQL with a random value column

It's possible to order data randomly with MySQL using ORDER BY RAND() or doing a count on the table and then using LIMIT to choose a random offset. However neither of these are suitable for dealing with large tables with many rows. In this post I present an alternative to these but it does require modifications to the table and additional business logic and is still not perfect.

Add a "random value" column

This solution requires adding an additional column on the table (and in turn additional business logic) so you should really only use this solution if you need to get random data frequently in a timely manner, such as on publicly accessible pages on a website.

So, if you are sure you really do frequently need some data ordered randomly from a large table, add this column:

ALTER TABLE `mytable` ADD `RandomValue` FLOAT NOT NULL,
ADD INDEX ( `RandomValue` );

Unfortunately it's not possible to make the default value for a column be RAND() , so the values must be initialised separately and business logic added every time an INSERT is done:

UPDATE mytable SET RandomValue = RAND();

Whenever a new record is added to the table make sure the RandomValue column is set:

INSERT INTO mytable (fields ..., RandomValue) VALUES (data ..., RAND());

Selecting a record at random

This needs to be done in a combination of SQL and scripting language. In this example I will use PHP because that's what I code with. $pdo is an already established connection to the MySQL database using PHP's PDO Data Object.

$row = false;
while(!$row) {
   
    $stmt = $pdo->query("SELECT RAND() as rand");
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    $rand = $row['rand'];

    $stmt = $pdo->query("SELECT * FROM test WHERE RandomValue > $rand ORDER BY RandomValue LIMIT 1");
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
   
}

Lines 4 to 6 get a random number from the database. I use a SQL query to get this from the database to ensure the type and number is compatible with what's stored in the RandomValue field. I could not achieve this using PHP's rand() or mt_rand() functions.

Lines 8 and 9 then fetch the record from the database by finding the record whose RandomValue is greater than the random number selected.

Because $rand could be less than MIN(RandomValue) both queries are wrapped within a while loop which keeps going until a row is found. I have seen similar solutions to mine which then look for the RandomValue < $rand but this weights the lowest RandomValue so it will be selected more frequently than the other numbers.

Why not just do "SELECT * FROM test WHERE RandomValue > RAND() ORDER BY RandomValue LIMIT 1" instead of doing the first query to get a random number?

I did try doing that but when I tested it by running the query several thousand times against a table with 9 equally spaced random values from 0.1 to 0.9, but for some reason the lower numbers were selected more frequently and 0.8 and 0.9 almost not at all.

When I changed the query to select a number first and then code that into the query string on the second query I got an almost even distribution each time. Note that this was of course on an ideally spaced RandomValue table, created entirely for testing the even-ness of distribution.

Pros and Cons

The pros for using this method is that it's almost instant to get the record from the database because the random value is indexed. Compare this with ORDER BY RAND() and using LIMIT offsets on larger tables which can get very slow.

The cons on the other hand make this not the most suitable solution, but it does work and is quick.

1. There needs to be an additional column in the database and business logic needs to be added so that every time a new record is inserted it sets a random value for this field. (Noting again that the default value for the column cannot be rand()).

2. It is potentially possible that two or more records could have the same random value resulting in only one of them ever being selected at "random".

3. If the number of records is not particularly large, and/or the random spread of numbers is not particuarly even, then some records are more likely to be selected than others.

4. It is potentially possible (although unlikely) that the loop will get stuck infinitely if the random numbers selected are always less than the lowest number stored in the database.

Conclusion

Fetching random records from a MySQL database is tricky. The easy and obvious solutions work fine until the table starts to have a decent number of records and then the queries take longer and longer to run as the tables get bigger.

I have seen many "solutions" while researching this which easily fail as a proper random solution; the most common of which (other than those I've already presented in earlier posts) is to work out the minimum and maximum values of a primary key, select a random number based on those and either select using = or >=. This particular solution pre-supposes that the primary key will always run in sequence with no holes because the = solution will fail if the record no longer exists and the >= solution has greater weight for records directly after a hole.

The solution I have presented here also has greater weight for records that have a greater gap between the RandomValue if the values are not more or less evenly populated. The smaller the table, the less even the distribution will be.

So in conclusion, there are many approaches to choosing a random record from a MySQL table but none is perfect: they either get too slow as tables increase in size, or are fast but not genuinely random as some records will have more weight than others.

Related posts:

• MySQL's LIMIT syntax can be slow for large offsets
• An alternative to ORDER BY RAND() for MySQL
• Randomly ordering a MySQL result set

"Randomly" order data across multiple pages with MySQL

Last week I looked at how to randomly order a MySQL result set by using ORDER BY RAND(). This is useful for a one-off query in which you need to draw a prize winner or similar but isn't so useful if you need to page through the resultset across several pages, because the order will change as you move from page to page.

Other issues with using ORDER BY RAND() when paging are:

• if you page forward or back to the pages you were aready on the data will be different from the first time you looked at that page
• you may see the same records on the next page as you saw on the previous page
• ordering using the RAND function is slow because it has to apply the RAND function to every row in the resultset which can be slow if it's large

There are a couple of fairly simple solutions which are detailed below.

Seed RAND with a value

Seeding the RAND function with a value in MySQL will result in a repeatable sequence of column values. Each time the query is executed the order of records will be the same. The seed value could then be passed from page to page to ensure the same order is used.

For example, where the seed value is 12:

SELECT ...
FROM ...
WHERE ...
ORDER BY RAND(12)

An advantage of using this solution is that it doesn't require any additional columns or periodic updates to the database, and you could have a different random resultset for each visitor; the seed value could be randomly selected in code on the first page and then passed from page to page.

The obvious disadvantage is that this will be slow for large resultsets.

Store a random number in the database which is updated regularly

Another option is to instead have an additional indexed column in the table which stores a randomly generated value. This is then updated periodically e.g. once a day.

The query to get data would then be along these lines, where my_random_column is the column that stores the random value:

SELECT ...
FROM ...
WHERE ...
ORDER BY my_random_value

A periodic process to update the table would do this:

UPDATE ...
SET my_random_value = RAND()

The advantage of using this method is that it isn't as slow as using RAND() every time the query is run and can use the index to help sort the data.

The disadvantages are that it requires an additional column in the table; an automatic process to re-randomize the value on a periodic basis; additional code to create the random value whenever a new record is created; and the additional process may cause extended locks on the table while the update is running depending on your storage engine.

Related posts:

• Randomly ordering a MySQL result set
• Using "group by" and "having" with MySQL
• Find the length of the longest string in MySQL