Wie wähle ich einen zufälligen Datensatz in MySQL effizient aus?
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 |
+----+-------------+-------+------+---------------+------+---------+------+-------+---------------------------------+
Die oben genannten Punkte gelten nicht als effizient. Wie soll ich das richtig machen?
AKTUALISIERE
Scheint, die in der Antwort erwähnte Lösung zu verwenden, hilft immer noch nicht:
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 |
+----+-------------+------------+--------+---------------+------+---------+------+-------+------------------------------+