Efficient Random Rows in MySQL

MySQL's official documentation dictates the use of the ORDER BY RAND() (coupled with LIMIT if you want) to select one or more random rows from a table. This is useful for small tables but as the number of rows increase, you will notice that this will be very inefficient due to the use of temporary tables (which are I/O bound) and filesorts.

I came up with two efficients solutions - one way is to use an external script or language and generate a random value and selecting this value from the id column (which is normally a clustered index) of the table. Fairly easy and I won't discuss it here unless asked. What I want to show you in this article is how to use a total MySQL solution to efficiently retrieve a random row.

The idea is as follows:

1. Generate a random number from 0.1 to 0.9
2. Map this random number to a table id
3. Select the row whose id is generated from previous step

In SQL this would be:

SELECT @r:=RAND();
SELECT @i:=
 (SELECT MIN(id) FROM table1 WHERE id>=
  (SELECT (@r*(SELECT MAX(id) FROM table1))));
SELECT * FROM table1 WHERE id=@i;

Although it takes three steps to generate the random row, this is very efficient and fast.