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 <j...@bytesmiths.com>: > 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 <mo...@fastmail.fm> >>> >>> >>> 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=luis.daniel.lu...@gmail.com > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
