List:General Discussion« Previous MessageNext Message »
From:Rick James Date:May 24 2012 2:58pm
Subject:RE: large temp files created by mysql
View as plain text  
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