MySQL RAND() Issues [was Re: How to Shuffle data]

> From: Reindl Harald
>=20
> do not use any random-functions of mysql even if they exists
> http://bugs.mysql.com/bug.php?id=3D59253

Of course, it depends on the desired quality of randomness needed.

I'm using RAND() to select random quotations to put at the end of =
emails. I can easily repeat the process by re-selecting the "Signature:" =
menu in Apple Mail. Problem is, I often notice that doing so cycles =
through several similar signatures in a decidedly non-random way!

(You can demo this by sending email to , with a =
search term in the Subject: line.)

But of course, a nuclear plant is not going to melt down because of my =
signature line.

So RAND() can be useful, but it is not really very random, and should be =
used with caution. My guess is that it's using Knuth's linear =
congruential algorithm that has well-known problems, but that =
unfortunately has been hidden deep in system code libraries since =
"Fundamental Algorithms" was published in 1968.

----------------
World events tend to be driven by loose coalitions of economic, =
political, and military interests, which function like guilds of species =
in an ecosystem. These guilds generate patterns of events that meet the =
interests of these coalitions, without there being any unity of purpose =
or clear plan. When powerful players accept they are not all-powerful, =
they increase their effectiveness, but are also able to deny and cover =
any responsibility for the adverse outcomes of those actions. -- David =
Holmgren
:::: Jan Steinman, EcoReality Co-op ::::


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: MySQL RAND() Issues [was Re: How to Shuffle data]

--bcaec501675d39531104a81f98c2
Content-Type: text/plain; charset=ISO-8859-1

This would be sooo much simpler to solve in MS-SQL, given the function
NewID(), which is guaranteed to return a unique value. I have used this in a
few web sites and it works splendidly; something along the lines of

SELECT TOP 10 *, NewID() FROM User_Messages
ORDER BY NewID

which is guaranteed to produce a new GUID for each row, and then order by
said values. It is not guaranteed to produce 10 results different than the
previous SELECT, but nevertheless works extremely well on a web site.

I have Googled this and that but not yet succeeded in finding the equivalent
in the MySQL world. In theory, this should be relatively easy, since the
GUID docs are available, but I'm out of my depth here on how to write the
MySQL equivalent function.

In case anyone has devised a MySQL equivalent for the NewID() function, we
could all massively benefit from your posting of same.

TIA,
Arthur

On Fri, Jul 15, 2011 at 1:40 PM, Jan Steinman wrote:

> > From: Reindl Harald
> >
> > do not use any random-functions of mysql even if they exists
> > http://bugs.mysql.com/bug.php?id=59253
>
> Of course, it depends on the desired quality of randomness needed.
>
> I'm using RAND() to select random quotations to put at the end of emails. I
> can easily repeat the process by re-selecting the "Signature:" menu in Apple
> Mail. Problem is, I often notice that doing so cycles through several
> similar signatures in a decidedly non-random way!
>
> (You can demo this by sending email to , with a
> search term in the Subject: line.)
>
> But of course, a nuclear plant is not going to melt down because of my
> signature line.
>
> So RAND() can be useful, but it is not really very random, and should be
> used with caution. My guess is that it's using Knuth's linear congruential
> algorithm that has well-known problems, but that unfortunately has been
> hidden deep in system code libraries since "Fundamental Algorithms" was
> published in 1968.
>
> ----------------
> World events tend to be driven by loose coalitions of economic, political,
> and military interests, which function like guilds of species in an
> ecosystem. These guilds generate patterns of events that meet the interests
> of these coalitions, without there being any unity of purpose or clear plan.
> When powerful players accept they are not all-powerful, they increase their
> effectiveness, but are also able to deny and cover any responsibility for
> the adverse outcomes of those actions. -- David Holmgren
> :::: Jan Steinman, EcoReality Co-op ::::
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=fuller.artful@gmail.com
>
>

--bcaec501675d39531104a81f98c2--

Re: MySQL RAND() Issues [was Re: How to Shuffle data]

I have found this approach pretty effective:

select *, rand() r from [mytable] where [condition] order by r limit 1=
0

as long as you are aware that a random number is generated for every
row in the table.

- michael dykman

On Fri, Jul 15, 2011 at 2:12 PM, Arthur Fuller wr=
ote:
> This would be sooo much simpler to solve in MS-SQL, given the function
> NewID(), which is guaranteed to return a unique value. I have used this i=
n a
> few web sites and it works splendidly; something along the lines of
>
> SELECT TOP 10 *, NewID() FROM User_Messages
> ORDER BY NewID
>
> which is guaranteed to produce a new GUID for each row, and then order by
> said values. It is not guaranteed to produce 10 results different than th=
e
> previous SELECT, but nevertheless works extremely well on a web site.
>
> I have Googled this and that but not yet succeeded in finding the equival=
ent
> in the MySQL world. In theory, this should be relatively easy, since the
> GUID docs are available, but I'm out of my depth here on how to write the
> MySQL equivalent function.
>
> In case anyone has devised a MySQL equivalent for the NewID() function, w=
e
> could all massively benefit from your posting of same.
>
> TIA,
> Arthur
>
> On Fri, Jul 15, 2011 at 1:40 PM, Jan Steinman wrote:
>
>> > From: Reindl Harald
>> >
>> > do not use any random-functions of mysql even if they exists
>> > http://bugs.mysql.com/bug.php?id=3D59253
>>
>> Of course, it depends on the desired quality of randomness needed.
>>
>> I'm using RAND() to select random quotations to put at the end of emails=
.. I
>> can easily repeat the process by re-selecting the "Signature:" menu in A=
pple
>> Mail. Problem is, I often notice that doing so cycles through several
>> similar signatures in a decidedly non-random way!
>>
>> (You can demo this by sending email to , with a
>> search term in the Subject: line.)
>>
>> But of course, a nuclear plant is not going to melt down because of my
>> signature line.
>>
>> So RAND() can be useful, but it is not really very random, and should be
>> used with caution. My guess is that it's using Knuth's linear congruenti=
al
>> algorithm that has well-known problems, but that unfortunately has been
>> hidden deep in system code libraries since "Fundamental Algorithms" was
>> published in 1968.
>>
>> ----------------
>> World events tend to be driven by loose coalitions of economic, politica=
l,
>> and military interests, which function like guilds of species in an
>> ecosystem. These guilds generate patterns of events that meet the intere=
sts
>> of these coalitions, without there being any unity of purpose or clear p=
lan.
>> When powerful players accept they are not all-powerful, they increase th=
eir
>> effectiveness, but are also able to deny and cover any responsibility fo=
r
>> the adverse outcomes of those actions. -- David Holmgren
>> :::: Jan Steinman, EcoReality Co-op ::::
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=3Dfuller.artful@gmail.com
>>
>>
>



--=20
=A0- michael dykman
=A0- mdykman@gmail.com

=A0May the Source be with you.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: MySQL RAND() Issues [was Re: How to Shuffle data]

In the last episode (Jul 15), Arthur Fuller said:
> This would be sooo much simpler to solve in MS-SQL, given the function
> NewID(), which is guaranteed to return a unique value. I have used this
> in a few web sites and it works splendidly; something along the lines of
>
> SELECT TOP 10 *, NewID() FROM User_Messages ORDER BY NewID
>
> which is guaranteed to produce a new GUID for each row, and then order by
> said values. It is not guaranteed to produce 10 results different than
> the previous SELECT, but nevertheless works extremely well on a web site.
>
> I have Googled this and that but not yet succeeded in finding the
> equivalent in the MySQL world. In theory, this should be relatively easy,
> since the GUID docs are available, but I'm out of my depth here on how to
> write the MySQL equivalent function.

http://dev.mysql.com/doc/refman/5.5/en/miscellaneous-functio ns.html#function_uuid

The UUID() function is that you want. It generates a GUID just like
NewID(), but the problem is that a GUID is only guaranteed to be unique, not
random:

mysql> select uuid() union select uuid() union select uuid();
+--------------------------------------+
| uuid() |
+--------------------------------------+
| 11a5cfd1-af13-11e0-80f5-0019b9df7547 |
| 11a5d054-af13-11e0-80f5-0019b9df7547 |
| 11a5d092-af13-11e0-80f5-0019b9df7547 |
+--------------------------------------+

(your output may vary depending on your OS'es preferred type of UUID to
generate). You could use md5(uuid()), however, which hashes your unique
values to get a nice large pseudorandom number:

mysql> select md5(uuid()) union select md5(uuid()) union select md5(uuid());
+----------------------------------+
| md5(uuid()) |
+----------------------------------+
| 6faefaf3f7bb9ba0d1e7a44cf6a9b1da |
| 740135ab69a1825630aeaf475b39f8b8 |
| 5c91a9132ad3e49e098e41d573de8e00 |
+----------------------------------+


--
Dan Nelson
dnelson@allantgroup.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: MySQL RAND() Issues [was Re: How to Shuffle data]

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



Am 15.07.2011 19:40, schrieb Jan Steinman:
>> From: Reindl Harald
>>
>> do not use any random-functions of mysql even if they exists
>> http://bugs.mysql.com/bug.php?id=3D59253
>=20
> So RAND() can be useful, but it is not really very random

the problem is the idiotic temp table
fecth a random record with it out of a tbale with
few thousand of records and pray to god that your
website has not much users because mysqld will
bring your machine down


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

iEYEARECAAYFAk4goagACgkQhmBjz394Anl4DwCePY6ZxaOJSoM8KeYaeNDd Hc0E
MMYAmgMkRg4wPFb+SpPS0mKpwqk19X7c
=tFUW
-----END PGP SIGNATURE-----

--------------enigF79B442B5C7476BD0F95B424--