List:General Discussion« Previous MessageNext Message »
From:Reindl Harald Date:October 24 2011 10:31pm
Subject:Re: large temp files created by mysql
View as plain text  

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
_______________

SELECT pri_key FROM table ORDER BY RAND() LIMIT 1;

let's say 'pri_key' as a autoincrement integer
let's say the table has some blob-fields and is 4 GB large

fetch ONE random id via mysql will explode your webserver
because 4 GB data is copied - there is no valid reason for
such crappy implementation, really no single reason




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