For my propouses aprox is fine. I guess for others it doesnt El may 24, 2012 9:59 a.m., "Rick James" <rja...@yahoo-inc.com> escribió:
> 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 > >
