List:General Discussion« Previous MessageNext Message »
From:mos Date:October 24 2011 4:02pm
Subject:Re: large temp files created by mysql
View as plain text  
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. :)
You need to choose a random row by using an auto-inc field. Something like:

select id from table where id>=myrandomnum limit 1

Here is the complete article:
http://www.greggdev.com/web/articles.php?id=6

Keep in mind that if there are a lot of holes in 
your table where id is not sequential, then the 
randomness will favor the number following the 
missing ids. So if you had rows with id's from 1 
to 1000, but the rows 100 to 199 were missing, 
then the number 200 will appear 100x more often 
than any other random number (assuming there were 
no other missing numbers). So for this to be as 
random as possible, then you should keep the 
missing id's as small as possible. The other 
solution is to renumber a non-autoinc column 
every so often to make sure the numbers are truly 
sequential and of course use a stored procedure 
to find the next highest number when adding new rows.

Mike


>Am 24.10.2011 17:03, schrieb Joey L:
>
>On Mon, Oct 24, 2011 at 10:52 AM, Joey L <mjh2000@stripped> wrote:
>
> > I have a very large table - approx 3 or 4 gig in size.
> > When i initiate a process on my webpage - mysql starts to create a
> > temporary table in /tmp directory.
> > Is there a way i can create this file/table ahead of time so mysql
> > does not have to create it ?
> >
> > thanks
> > mjh
> >
>Sorry - it is 30 gig file .
>
>
>
>--
>
>Mit besten Grüßen, Reindl Harald
>the lounge interactive design GmbH
>A-1060 Vienna, Hofmühlgasse 17
>CTO / software-development / cms-solutions
>p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
>icq: 154546673, http://www.thelounge.net/
>
>http://www.thelounge.net/signature.asc.what.htm
>
>
>

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