Maybe. 1. The "Rows" is approximate, so you could over-shoot or under-shoot the end of the table.
2. OFFSET (limit $r,1) still has to scan $r rows. 3. SELECT * with an OFFSET will read the entire rows. SELECT `foo`, where `foo` is indexed, but not the InnoDB PRIMARY KEY, will scan only the INDEX(foo). This is likely to be much faster. But you are unlikely to do that unless foo is UNIQUE. A slight improvement (addressing both issues) is to decide which end to approach from. But scanning from the end needs an ORDER BY, preferably on the PRIMARY KEY. etc. > -----Original Message----- > From: Luis Daniel Lucio Quiroz [mailto:luis.daniel.lu...@gmail.com] > Sent: Thursday, May 24, 2012 12:00 AM > To: Jan Steinman > Cc: mysql@lists.mysql.com > Subject: Re: large temp files created by mysql > > 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
