From: Reindl Harald
Date: October 24 2011 10:31pm
Subject: Re: large temp files created by mysql
List-Archive: http://lists.mysql.com/mysql/226176
Message-Id: <4EA5E74F.2020309@thelounge.net>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
 protocol="application/pgp-signature";
 boundary="------------enigA3D632959093E4061BAAF5E8"

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



Am 24.10.2011 23:31, schrieb mos:
> At 11:32 AM 10/24/2011, Reindl Harald wrote:
>=20
>=20
>> 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=
=2E This of course is extremely inefficient. :)
>> > You need to choose a random row by using an auto-inc field. Somethin=
g like:
>> >
>> > select id from table where id>=3Dmyrandomnum limit 1
>>
>> but this is TOTALLY braindead if "id" is a primary-KEY with auto-incre=
ment
>=20
> It all depends on how many holes you have in the sequence and how rando=
m 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 anot=
her table with the columns bin and an
> autoinc column and pick one of those rows randomly. Regenerate the tabl=
e once an hour or once a day.
>=20
> Either way it is going to be a LOT FASTER than sorting the entire table=


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
_______________

SELECT pri_key FROM table ORDER BY RAND() LIMIT 1;

let's say 'pri_key' as a autoincrement integer
let's say the table has some blob-fields and is 4 GB large

fetch ONE random id via mysql will explode your webserver
because 4 GB data is copied - there is no valid reason for
such crappy implementation, really no single reason




--------------enigA3D632959093E4061BAAF5E8
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/

iEYEARECAAYFAk6l508ACgkQhmBjz394AnkvDQCfc6yeiks/ivXKMcjWavwoqxRx
m94AoIKNukgN0VOBf7gK0tWLvWmP3qGk
=3hkh
-----END PGP SIGNATURE-----

--------------enigA3D632959093E4061BAAF5E8--