StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>A lot of people seem to be convinced that <code>ORDER BY RAND()</code> is somehow able to produce results without scanning the whole table.</p> <p>Well it isn't. In fact, it's liable to be slower than ordering by column values, because MySQL has to call the RAND() function for each row.</p> <p>To demonstrate, I made a simple table of half a million MD5 hashes:</p> <pre><code>mysql> select count(*) from delete_me; +----------+ | count(*) | +----------+ | 500000 | +----------+ 1 row in set (0.00 sec) mysql> explain delete_me; +-------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | txt | text | NO | | NULL | | +-------+------------------+------+-----+---------+----------------+ 2 rows in set (0.12 sec) mysql> select * from delete_me limit 4; +----+----------------------------------+ | id | txt | +----+----------------------------------+ | 1 | 9b912c03d87991b71955a6cd4f81a299 | | 2 | f1b7ddeb1c1a14265a620b8f2366a22e | | 3 | 067b39538b767e2382e557386cba37d9 | | 4 | 1a27619c1d2bb8fa583813fdd948e94c | +----+----------------------------------+ 4 rows in set (0.00 sec) </code></pre> <p>Using <code>ORDER BY RAND()</code> to choose a random row from this table takes my computer 1.95 seconds.</p> <pre><code>mysql> select * from delete_me order by rand() limit 1; +--------+----------------------------------+ | id | txt | +--------+----------------------------------+ | 446149 | b5f82dd78a171abe6f7bcd024bf662e8 | +--------+----------------------------------+ 1 row in set (1.95 sec) </code></pre> <p>But ordering the text fields in ascending order takes just 0.8 seconds.</p> <pre><code>mysql> select * from delete_me order by txt asc limit 1; +-------+----------------------------------+ | id | txt | +-------+----------------------------------+ | 88583 | 00001e65c830f5b662ae710f11ae369f | +-------+----------------------------------+ 1 row in set (0.80 sec) </code></pre> <p>Since the id values in this table are numbered sequentially starting from 1, I can choose a random row much more quickly like this:</p> <pre><code>mysql> select * from delete_me where id=floor(1+rand()*500000) limit 1; +-------+----------------------------------+ | id | txt | +-------+----------------------------------+ | 37600 | 3b8aaaf88af68ca0c6eccff7e61e897a | +-------+----------------------------------+ 1 row in set (0.02 sec) </code></pre> <p>But in the general case, I would suggest using the <a href="https://stackoverflow.com/a/3312281/1679849">method proposed by Mike</a> in the page linked to by @deceze.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload