Selecting a random row from a MySQL table is a pretty common requirement for many web sites/apps but for some reason it remains a bit of a mystery to many people. There are several ways to approach this problem but they all have something in common: the larger the table, the slower your query will be.
With smaller tables, the time-saving is negligible and the method chosen doesn’t really matter.
- Grab all the rows and then pick one randomly in your script
This is by far the simplest method, and what most beginners would do. It might even work fine for very small tables, but is actually terribly inefficient and would really be a big mistake on all but the very smallest of tables.
- Good old “ORDER BY RAND()“
SELECT * FROM `table` ORDER BY RAND() LIMIT 1
An intermediate coder might take this approach, thinking that it’s a smart move. This performs very well on small tables but isn’t scalable at all. A query like this causes MySQL to create a temporary table for all results and assign each row a random sorting index. Yikes.
- A better way
We can select a random number and then pick the row that matches it (or the closest row if there is no exact match). This of course assumes that the table has a unique id column, which is true in the vast majority of cases. First, we’ll need to get a random number between 1 and MAX(id):SELECT FLOOR( MAX(id) * RAND() ) FROM `table`
We’re basically multiplying the highest value of id by a number between 0 and 1, and then flooring it to get an integer which we’ll use to select a row. To make sure we don’t get messed up in the case that some values of id may be missing (if rows were deleted at some point), we’ll have to use the >= operator instead of a simple =. So, our query above will actually become a subquery of something like this:
SELECT * FROM `table` WHERE id >= ( SELECT FLOOR( MAX(id) * RAND() ) FROM `table` ) LIMIT 1
This should give us what we need and is much more scalable than the first two options.
