I got a solution maybe
step 1:
mysql> explain select * from users;
+----+-------------+-------+------+---------------+------+---------+------+----------+-------+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------+
| 1 | SIMPLE | users | ALL | NULL | NULL | NULL |
NULL | 32883093 | |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------+
1 row in set (0.00 sec)
so you get the "rows" field
Step2:
select * from users, limit $r,1
What do you think? Is the only way i found what delays seconds not
minuts. USERS is a 19GB Table for me.
LD
2011/10/30 Jan Steinman <Jan@stripped>:
> Actually, having tried that, you still need the ORDER BY RAND() in there. Otherwise,
> I keep getting the same record over and over. But it surely cuts way down on the number of
> rows that need to be sorted.
>
> So if your table size is fairly stable, and you pick a good number for the WHERE
> constant, you can make this quite speedy.
>
> Still, it seems there should be a better way...
>
> On 30 Oct 11, at 18:51, Jan Steinman wrote:
>
>>> From: mos <mos99@stripped>
>>>
>>>
>>> At 10:34 AM 10/24/2011, you wrote:
>>>> select id from table order by rand() limit 1;
>>>> is doing as example a dumb temporary table with the full size
>>>
>>> Because it has to sort the entire table, then it returns the one row. This of
> course is extremely inefficient. :)
>>
>> That is absolutely incredible and counter-intuitive, and (as you say) extremely
> inefficient!
>>
>> This is used everywhere. Perhaps it is one of the biggest "anti-patterns" in SQL.
> I just checked two different SQL "cookbook" sites, and they both recommend ORDER BY
> RAND().
>>
>> I just googled around a bit, and found that putting RAND() in the WHERE clause is
> very efficient:
>>
>> SELECT id FROM table WHERE RAND() < 0.01 LIMIT 1
>>
>> The comparison constant can be optimized for the number of rows you have. The
> above returns the first record of 1% of the table. If you have a million rows, you might
> want to bump that to something like 100 parts per million or so.
>>
>> But really, folks, this is something so ubiquitous and so recommended, why can't
> the query optimizer look out for ORDER BY RAND() and simply skip the table sort and just
> grab some record? (Hopefully using something better than Knuth's LCRNG...)
>>
>> ----------------
>> Learning to think wholistically requires an overriding, or reversal, of much of
> the cultural heritage of the last few hundred years. -- David Holmgren
>> :::: Jan Steinman, EcoReality Co-op ::::
>>
>
> ----------------
> Within a few human generations, the low-energy patterns observable in natural
> landscapes will again form the basis of human system design after the richest deposits of
> fossil fuels and minerals are exhausted. -- David Holmgren
> :::: Jan Steinman, EcoReality Co-op ::::
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>
