List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:December 9 2001 4:26am
Subject:Re: Random
View as plain text  
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
>>  Try
>>  ("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
RandomRob@TH8 Dec
  • Re: RandomKodrik8 Dec
  • Re: RandomRob@TH8 Dec
    • Re: RandomJason Wong9 Dec
    • Re: RandomPaul DuBois9 Dec
      • Re: RandomJason Wong9 Dec
  • Re: RandomShankar Unni8 Dec