At 3:19 PM -0800 12/8/01, Rob@TH wrote:
>Hmm still nothing :/
>Any other possibilities?
The suggestion offered below is a workaround that seems logical given
the lack of ORDER BY RAND() prior to MySQL 3.23. Unfortunately, it
doesn't work due to the behavior of the optimizer in pre-3.23 versions.
Specifically, MySQL notices that "rand() as rnd" is a function, deduces
(incorrectly) that the contents of the column will be constant, and thus
optimizes away the "order by rnd" clause entirely. Result: no sorting.
To deal with this, use an expression that includes RAND(), but written
in such a way that the optimizer won't think is constant. For example,
if you have an id column, do something like this:
SELECT id*0+RAND() AS rnd, ... FROM tbl_name ORDER BY rnd LIMIT 1;
>----- Original Message -----
>From: "Kodrik" <kodrik@stripped>
>To: "Rob@TH" <yobobo@stripped>; <mysql@stripped>
>Sent: Saturday, December 08, 2001 8:45 AM
>Subject: Re: Random
>> ("select field 1, field2, field3, rand() as rnd
>> where blah blah blah
>> order by rnd limit 1")
>> On Saturday 08 December 2001 10:35 am, yobobo@stripped wrote:
>> > Hi,
>> > I am trying to pull up one random name in mysql 3.22.21 but not having
>> > luck.
>> > Here is what I have done:
>> > $select=$dbh->prepare("select * from $thetable where url is not null
>> > by rand() limit 1");
>> > I have tried everything, but nothing seems to work. Can anyone help.
> > > Rob