“Show a random quote.” “Pick 5 random winners.” “Sample 1% of the rows.” Random selection is a normal need, and the obvious solution has a hidden cost.
SET SESSION cte_max_recursion_depth = 20000;
CREATE TABLE quotes (
id INT AUTO_INCREMENT PRIMARY KEY,
body VARCHAR(40) NOT NULL
);
INSERT INTO quotes (body)
WITH RECURSIVE n AS (SELECT 1 AS i UNION ALL SELECT i + 1 FROM n WHERE i < 10000)
SELECT CONCAT('Quote number ', i) FROM n;The simple way
SELECT id, body FROM quotes ORDER BY RAND() LIMIT 1;+------+---------------------+
| id | body |
+------+---------------------+
| 4821 | Quote number 4821 |
+------+---------------------+
1 row in set(The row is different on every run, so the output is not compared.) ORDER BY RAND() gives every row a random number, sorts all of them, and then keeps the first. On 100 rows that is nothing. On a million rows it creates a huge temporary table and takes seconds, on every page view.
EXPLAIN FORMAT=TRADITIONAL SELECT id, body FROM quotes ORDER BY RAND() LIMIT 1;+----+-------------+--------+------+---------------+------+------+------+------+----------+----------------------------------------------+
| 1 | SIMPLE | quotes | ALL | NULL | NULL | NULL | NULL | 9968 | 100.00 | Using temporary; Using filesort |
+----+-------------+--------+------+---------------+------+------+------+------+----------+----------------------------------------------+Using temporary; Using filesort is the warning sign.
Faster: pick a random id
If ids are dense (few gaps), generate a random id first:
SET @max_id = (SELECT MAX(id) FROM quotes);
SELECT id, body FROM quotes WHERE id >= 1 + FLOOR(RAND() * @max_id) ORDER BY id LIMIT 1;+------+-------------------+
| id | body |
+------+-------------------+
| 7301 | Quote number 7301 |
+------+-------------------+
1 row in setThis uses the primary key and reads one row, however big the table is. Rows after a large gap are a little more likely to be chosen, which is fine for “a random quote”, but not for fair draws.
Several random rows
SELECT q.id, q.body
FROM quotes q
JOIN (SELECT FLOOR(1 + RAND() * (SELECT MAX(id) FROM quotes)) AS id FROM quotes LIMIT 20) r ON r.id = q.id
LIMIT 5;This picks up to 20 random ids and returns the 5 that exist. Because random numbers can repeat and fall in gaps, you may get fewer than 5. Loop in your application until you have enough.
A reproducible random order
RAND(seed) gives the same sequence for the same seed. That is useful for “shuffle with a stable result” (the same order on every page of a paginated shuffled list, for example):
SELECT id FROM quotes ORDER BY RAND(42) LIMIT 3;
SELECT id FROM quotes ORDER BY RAND(42) LIMIT 3;+------+
| id |
+------+
| 1259 |
| 9814 |
| 8708 |
+------+
3 rows in set
+------+
| id |
+------+
| 1259 |
| 9814 |
| 8708 |
+------+
3 rows in setBoth queries return the same three ids. (The exact numbers depend on the data and the MySQL version.)
Sampling a fraction
SELECT COUNT(*) AS sampled FROM quotes WHERE RAND(7) < 0.01;+---------+
| sampled |
+---------+
| 97 |
+---------+About 1 percent of the rows. This reads the whole table, but it does not sort, which is much cheaper than ORDER BY RAND().
Fair draws
For a fair prize draw among eligible rows, use ORDER BY RAND() on the eligible set only (a small one), or choose by row number with a window function. Do not use the “random id” trick, because gaps make it unfair.
A precomputed random column
For a feed that must show random items quickly, add an indexed column with a random number, refreshed now and then:
ALTER TABLE quotes ADD COLUMN random_key DOUBLE NOT NULL DEFAULT 0, ADD INDEX idx_random (random_key);
UPDATE quotes SET random_key = RAND(1);
SELECT id FROM quotes WHERE random_key >= 0.5 ORDER BY random_key LIMIT 3;+----+
| id |
+----+
| 17 |
+----+Now the query is an index range scan. Refresh the column nightly to change the order.
Never use RAND() for security
RAND() is not cryptographically secure. For tokens, passwords and codes, generate random values in your application with a secure generator (random_bytes() in PHP).