List:General Discussion« Previous MessageNext Message »
From:mos Date:October 24 2011 9:31pm
Subject:Re: large temp files created by mysql
View as plain text  
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.

Miike


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