3 ms·
Can we please stop saying ORDER BY RAND/RANDOM is slow?
- andfarm 16y agoSadly, 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.
- deleted 16y ago[deleted]
- pinksoda 16y agoHe's using a small table with only 100,000 rows. Let's see him claim rand() is fast on 1m, 5m, or 10m rows.