List:General Discussion« Previous MessageNext Message »
From:Reindl Harald Date:October 25 2011 8:32am
Subject:Re: large temp files created by mysql
View as plain text  

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




Attachment: [application/pgp-signature] OpenPGP digital signature signature.asc
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