

Can we please stop saying ORDER BY RAND/RANDOM is slow? - compay
http://njclarke.com/posts/can-we-please-stop-saying-order-by-random-is-slow.html

======
andfarm
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.

------
pinksoda
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.

