List:General Discussion« Previous MessageNext Message »
From:mos Date:October 26 2011 3:14am
Subject:Re: large temp files created by mysql
View as plain text  
At 03:32 AM 10/25/2011, you wrote:


>Am 25.10.2011 05:45, schrieb mos:
> > At 05:31 PM 10/24/2011, Reindl Harald wrote:
> >
> >
> >> Am 24.10.2011 23:31, schrieb mos:
> >> > At 11:32 AM 10/24/2011, Reindl Harald wrote:
> >> >
> >> >
> >> >> Am 24.10.2011 18:02, schrieb mos:
> >> >> > 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
> >> >>
> >> >> but this is TOTALLY braindead if "id" is a primary-KEY with 
> auto-increment
> >> >
> >> > It all depends on how many holes you have in the sequence and 
> how random you want the selections to be. If there
> >> > are no holes then it will work. You need of course to get the 
> first and last id and generate "myrandomnum" within
> >> > that range. If there are a lot of holes in the sequence then 
> build another table with the columns bin and an
> >> > autoinc column and pick one of those rows randomly. Regenerate 
> the table once an hour or once a day.
> >> >
> >> > Either way it is going to be a LOT FASTER than sorting the entire
> table
> >>
> >> and why in the world is with the query above the WHOLE table
> >> copied in a temp-table while fecth the whole id-list in a
> >> php-array and take a random one is more than 1000 times faster?
> >>
> >> the implementation if "order by rand()" is totally braindead
> >
> > It is not "braindead". You told MySQL to sort by rand() which is 
> a non-indexed column.
> > It needs to assign a value to each row of the result set (all ids 
> of the table) and sort
> > it to get the lowest random number. This is very inefficient for 
> large tables.
>
>but there is mo need to do this with the whole table
>if the only requested field is the primary key

Sure but if the table has 100 million rows and you want 1 random id, 
that means sorting 100 million id's from the index to disk. This is 
still grossly inefficient. It may work fine on tables with a couple 
thousand rows, but not for million row tables. That's why the two 
methods I suggested don't use sorting.

Mike  

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