Choosing a random row from a table using SQL is a common task.
For small tables and when you don’t have a numeric key or don’t know the maximum value in advance, SELECT * FROM test ORDER BY RAND() LIMIT 1 is adequate. However, this involves calculating RAND() for each input row, followed by a sort.
When there is a numeric key from 1 to N and there are no gaps (ineligible records), a more optimized method is SELECT * FROM test WHERE id >= CEIL(N * RAND()) LIMIT 1.
A more difficult scenario is when there is no numeric key, there are rows in the table that are ineligible, and you don’t want to sort. In this case an artificial rownum column can be computed and used in the selection calculation:
SELECT id FROM (SELECT id, @n := @n+1 rownum FROM test, (SELECT @n := 0, @r := RAND()) d WHERE 1) x WHERE rownum = CEIL(@n * @r);
Replace “WHERE 1″ above with whatever conditions you need.
Xaprb: How to number rows in MySQL
Perl Cookbook: 8.6. Picking a Random Line from a File
