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.rei...@thelounge.net] >> Sent: Friday, April 20, 2012 12:50 AM >> To: mysql@lists.mysql.com >> 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
signature.asc
Description: OpenPGP digital signature
