List:General Discussion« Previous MessageNext Message »
From:Luis Daniel Lucio Quiroz Date:May 24 2012 4:21pm
Subject:RE: large temp files created by mysql
View as plain text  
For my propouses aprox is fine. I guess for others it doesnt
El may 24, 2012 9:59 a.m., "Rick James" <rjames@stripped> 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.lucio@stripped]
> > Sent: Thursday, May 24, 2012 12:00 AM
> > To: Jan Steinman
> > Cc: mysql@stripped
> > 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 <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
> > >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:    http://lists.mysql.com/mysql
>
>

Thread
large temp files created by mysqlJoey L24 Oct
  • Re: large temp files created by mysqlJoey L24 Oct
    • Re: large temp files created by mysqlReindl Harald24 Oct
      • Re: large temp files created by mysqlmos24 Oct
        • Re: large temp files created by mysqlJoey L24 Oct
          • Re: large temp files created by mysqlReindl Harald24 Oct
        • Re: large temp files created by mysqlReindl Harald24 Oct
          • Re: large temp files created by mysqlmos24 Oct
            • Re: large temp files created by mysqlReindl Harald25 Oct
              • Re: large temp files created by mysqlmos25 Oct
                • Re: large temp files created by mysqlReindl Harald25 Oct
                  • Re: large temp files created by mysqlmos26 Oct
                    • Re: large temp files created by mysqlReindl Harald26 Oct
Re: large temp files created by mysqlAntony T Curtis24 Oct
  • Re: large temp files created by mysqlJoey L24 Oct
    • Re: large temp files created by mysqlJohan De Meersman24 Oct
Re: large temp files created by mysqlJan Steinman31 Oct
  • Re: large temp files created by mysqlJan Steinman31 Oct
    • Re: large temp files created by mysqlLuis Daniel Lucio Quiroz24 May
      • RE: large temp files created by mysqlRick James24 May
        • RE: large temp files created by mysqlLuis Daniel Lucio Quiroz24 May