From: Reindl Harald
Date: October 25 2011 8:32am
Subject: Re: large temp files created by mysql
List-Archive: http://lists.mysql.com/mysql/226180
Message-Id: <4EA6741E.80000@thelounge.net>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
 protocol="application/pgp-signature";
 boundary="------------enigE007831C54045D73F930EB09"

--------------enigE007831C54045D73F930EB09
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable



Am 25.10.2011 05:45, schrieb mos:
> At 05:31 PM 10/24/2011, Reindl Harald wrote:
>=20
>=20
>> Am 24.10.2011 23:31, schrieb mos:
>> > At 11:32 AM 10/24/2011, Reindl Harald wrote:
>> >
>> >
>> >> Am 24.10.2011 18:02, schrieb mos:
>> >> > At 10:34 AM 10/24/2011, you wrote:
>> >> >> select id from table order by rand() limit 1;
>> >> >> is doing as example a dumb temporary table with the full size
>> >> >
>> >> > Because it has to sort the entire table, then it returns the one =
row. This of course is extremely
>> inefficient. :)
>> >> > You need to choose a random row by using an auto-inc field. Somet=
hing like:
>> >> >
>> >> > select id from table where id>=3Dmyrandomnum limit 1
>> >>
>> >> but this is TOTALLY braindead if "id" is a primary-KEY with auto-in=
crement
>> >
>> > It all depends on how many holes you have in the sequence and how ra=
ndom you want the selections to be. If there
>> > are no holes then it will work. You need of course to get the first =
and last id and generate "myrandomnum" within
>> > that range. If there are a lot of holes in the sequence then build a=
nother table with the columns bin and an
>> > autoinc column and pick one of those rows randomly. Regenerate the t=
able once an hour or once a day.
>> >
>> > Either way it is going to be a LOT FASTER than sorting the entire ta=
ble
>>
>> and why in the world is with the query above the WHOLE table
>> copied in a temp-table while fecth the whole id-list in a
>> php-array and take a random one is more than 1000 times faster?
>>
>> the implementation if "order by rand()" is totally braindead
>=20
> It is not "braindead". You told MySQL to sort by rand() which is a non-=
indexed column.=20
> It needs to assign a value to each row of the result set (all ids of th=
e table) and sort=20
> it to get the lowest random number. This is very inefficient for large =
tables.

but there is mo need to do this with the whole table
if the only requested field is the primary key




--------------enigE007831C54045D73F930EB09
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.11 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk6mdB4ACgkQhmBjz394AnkjDwCfckp/HbFafmTfhSabzfWXjIOB
VKkAnilwLCL072mONLQ30KpPEUgUi4Qk
=XbfJ
-----END PGP SIGNATURE-----

--------------enigE007831C54045D73F930EB09--