From: Reindl Harald
Date: April 20 2012 6:54pm
Subject: Re: Why does the limit use the early row lookup.
List-Archive: http://lists.mysql.com/mysql/227225
Message-Id: <4F91B0EE.2070802@thelounge.net>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
 protocol="application/pgp-signature";
 boundary="------------enig38ABD256A65D9FD9A979E418"

--------------enig38ABD256A65D9FD9A979E418
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

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 d=
ata first, then sort, then do the LIMIT.
>=20
> Potential optimizations include
>=20
> * Keep a "pointer", not the whole data.  (This may be practical for SEL=
ECT *, but not practical in other cases.)
>=20
> * Build a "priority queue" with only 10 items (in the case of LIMIT 10)=
=2E  The ORDER BY, instead of doing a regular sort, would insert into thi=
s queue.  This _may_ be more efficient because it would have to hold only=
 10 rows, not _all_ the rows.
>=20
>> -----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 =E5=BC=A0=E5=BF=97=E5=88=9A:
>>> 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 :-)
>=20

--=20

Mit besten Gr=C3=BC=C3=9Fen, Reindl Harald
the lounge interactive design GmbH
A-1060 Vienna, Hofm=C3=BChlgasse 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


--------------enig38ABD256A65D9FD9A979E418
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.12 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk+RsO4ACgkQhmBjz394Ann8qgCbBQcZ9DoH6GlUCCH8GtdFTEHR
3nkAoIGG4/GCEcEWuuaPXL3B2UobgM9t
=oq1t
-----END PGP SIGNATURE-----

--------------enig38ABD256A65D9FD9A979E418--