Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I select a random record efficiently in MySQL?

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 |
+----+-------------+------------+--------+---------------+------+---------+------+-------+------------------------------+
like image 838
user198729 Avatar asked Oct 15 '22 06:10

user198729


1 Answers

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.

like image 129
Mark Byers Avatar answered Oct 20 '22 21:10

Mark Byers