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:
- Generate a random number from 0.1 to 0.9
- Map this random number to a table id
- 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.