Can we please stop saying ORDER BY RAND/RANDOM is slow? (njclarke.com)

Sadly, while just selecting an id is faster than selecting the whole row, it's still a very slow operation overall. Here's the results on an 8-million-row production table:

   mysql> select * from large_table order by rand() limit 1;
   <...>
   1 row in set (36.69 sec)
   
   mysql> select primary_key_column from large_table order by rand() limit 1;
   <...>
   1 row in set (7.33 sec)

Basically, ORDER BY RAND() forces a temporary table / filesort no matter what; selecting fewer columns decreases the size of the temporary table, but doesn't actually eliminate the problem.

The best way to select a random row from a MySQL table is using a trick I got from Mediawiki: create an indexed float column, set it to RAND() for each row, and select random rows using:

   SELECT * FROM table WHERE randnum > RAND() ORDER BY randnum LIMIT 1

This runs as an index range scan, making it basically instantaneous.

He's using a small table with only 100,000 rows. Let's see him claim rand() is fast on 1m, 5m, or 10m rows.