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