Re: Why does the limit use the early row lookup.

[Reindl Harald]

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: [phone], m: [phone]
icq: 154546673, http://www.thelounge.net/

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


Attachment: [application/pgp-signature] OpenPGP digital signature signature.asc