mysql> EXPLAIN SELECT * FROM urls ORDER BY RAND() LIMIT 1;
+----+-------------+-------+------+---------------+------+---------+------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+---------------------------------+
| 1 | SIMPLE | urls | ALL | NULL | NULL | NULL | NULL | 62228 | Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+-------+---------------------------------+
The above doesn't qualify as efficient,how should I do it properly?
UPDATE
Seems using the solution mentioned in the answer still doesn't help:
mysql> explain SELECT *
-> FROM (
-> SELECT @cnt := COUNT(*) + 1,
-> @lim := 10
-> FROM urls
-> ) vars
-> STRAIGHT_JOIN
-> (
-> SELECT r.*,
-> @lim := @lim - 1
-> FROM urls r
-> WHERE (@cnt := @cnt - 1)
-> AND RAND(20090301) < @lim / @cnt
-> ) i;
+----+-------------+------------+--------+---------------+------+---------+------+-------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+------+---------+------+-------+------------------------------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | |
| 1 | PRIMARY | <derived3> | ALL | NULL | NULL | NULL | NULL | 10 | |
| 3 | DERIVED | r | ALL | NULL | NULL | NULL | NULL | 62228 | Using where |
| 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+------------+--------+---------------+------+---------+------+-------+------------------------------+
Quassnoi has written a post about selecting rows at random without performing a sort. His example selects 10 rows at random, but you can adapt it to select just one row.
If you want it to be really fast then you can use an approximation that won't be completely uniform or will sometimes fail to return a row.
You can also use a stored procedure to select a random row quickly from Bill Karwin's post:
SET @r := (SELECT ROUND(RAND() * (SELECT COUNT(*) FROM mytable)));
SET @sql := CONCAT('SELECT * FROM mytable LIMIT ', @r, ', 1');
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
Note that this will run much faster in MyISAM than InnoDB because COUNT(*) is expensive in InnoDB but nearly instant in MyISAM.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With