List:General Discussion« Previous MessageNext Message »
From:Reindl Harald Date:April 20 2012 6:54pm
Subject:Re: Why does the limit use the early row lookup.
View as plain text  
i know what it does, but it is simply idiotic
select pri_key_field from table order by rand() limit 10;

why in the world can this not be doe with an index?
only the auto_increment field is involved

soryy, no understanding

it is idiotic that you need to "select pri_key_field from table"
and fetch 10 random keys out of the large php-array to get
this done 1000 faster as mysql itself

there is no single reason to copy a 10 GB table for
fetching 10 integer values of a auto_increment

Am 20.04.2012 20:26, schrieb Rick James:
> Any ORDER BY (that cannot be done using an index) will gather all the data first,
> then sort, then do the LIMIT.
> 
> Potential optimizations include
> 
> * Keep a "pointer", not the whole data.  (This may be practical for SELECT *, but not
> practical in other cases.)
> 
> * Build a "priority queue" with only 10 items (in the case of LIMIT 10).  The ORDER
> BY, instead of doing a regular sort, would insert into this queue.  This _may_ be more
> efficient because it would have to hold only 10 rows, not _all_ the rows.
> 
>> -----Original Message-----
>> From: Reindl Harald [mailto:h.reindl@stripped]
>> Sent: Friday, April 20, 2012 12:50 AM
>> To: mysql@stripped
>> Subject: Re: Why does the limit use the early row lookup.
>>
>>
>>
>> Am 20.04.2012 04:29, schrieb 张志刚:
>>> My point is that the limit can use late row lookup: lookup rows after
>>> checking indexes to optimize the select speed.
>>>
>>> But the mysql optimizer do it with the early row lookup: lookup all
>>> rows before checking indexes when the one fetch column is not in the
>> indexes.
>>>
>>> Tell me why?
>>
>> because the mysql optimizer until now is really bad in many situations
>> - order by rand() makes a temporary table wil ALL data as example even
>> with limit
>>
>> select * from table order by rand() limit 10; reads and writes the
>> whole table to disk have fun with large tables :-)
> 

-- 

Mit besten Grüßen, Reindl Harald
the lounge interactive design GmbH
A-1060 Vienna, Hofmühlgasse 17
CTO / software-development / cms-solutions
p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
icq: 154546673, http://www.thelounge.net/

http://www.thelounge.net/signature.asc.what.htm


Attachment: [application/pgp-signature] OpenPGP digital signature signature.asc
Thread
Why does the limit use the early row lookup.张志刚20 Apr
  • Re: Why does the limit use the early row lookup.Reindl Harald20 Apr
    • RE: Why does the limit use the early row lookup.Rick James20 Apr
      • Re: Why does the limit use the early row lookup.Reindl Harald20 Apr
    • Re: Why does the limit use the early row lookup.Zhangzhigang23 Apr
      • Re: Why does the limit use the early row lookup.shawn green23 Apr
        • RE: Why does the limit use the early row lookup.Rick James23 Apr
  • RE: Why does the limit use the early row lookup.Rick James23 Apr
    • RE: Why does the limit use the early row lookup.Zhangzhigang24 Apr
      • RE: Why does the limit use the early row lookup.Rick James24 Apr
Re: Why does the limit use the early row lookup.Jan Steinman25 Apr