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

Am 26.10.2011 05:14, schrieb mos:
>> > 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.

this is not the topic
the topic is "order by rand()" works braindead
all other things are workarounds

and it still matters on small tables with only 512KB if mysqld
makes on each query a 512KB temp-file or not - have fun under
concurrent load even with small tables!

only the auto-ids are tiny data and nothing more is requested here
as result and no reason to copy the whole table with all fields around



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