List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:November 27 1999 5:13am
Subject:Re: randomizing returned rows
View as plain text  
At 7:13 PM -0800 11/26/99, John Rumpelein wrote:
>Folks,
>
>I'm running 3.22 here, and I'm trying to get MySQL to return rows in
>random order.  It seems to me that the following should work, but does
>not:
>
>mysql> select item_id,rand() as foo from item where item_id < 35 order
>by foo;
>+---------+--------+
>| item_id | foo    |
>+---------+--------+
>|      25 | 0.9642 |
>|      26 | 0.7723 |
>|      27 | 0.9688 |
>|      28 | 0.5272 |
>|      29 | 0.7295 |
>|      30 | 0.0660 |
>|      31 | 0.1416 |
>|      32 | 0.5099 |
>|      33 | 0.1248 |
>|      34 | 0.0944 |
>+---------+--------+
>10 rows in set (0.00 sec)
>
>Is there a proper way to do this?  If it is not obvious I am not
>really interested in the value of rand(), but I'm trying to use these
>to get the rows into a random order.  Any insight anyone can offer
>would be greatly appreciated.


 From an upcoming book on MySQL:

However, if you write the query as follows, the optimizer defeats your intent:
.Ps
SELECT ..., RAND() as rand_col FROM ... ORDER BY rand_col
.Pe
The problem here is that MySQL sees that the column is a function call and
thinks that the value of the column will be a constant, and optimizes the
.Cw ORDER
.Cw BY
clause right out of the query!
You can fool the optimizer by referring to a table column in the expression.
For example, if your table has a column named
.Cw age ,
you can write the query like this:
.Ps
SELECT ..., age*0+RAND() as rand_col FROM ... ORDER BY rand_col
.Pe

-- 
Paul DuBois, paul@stripped
Thread
randomizing returned rowsJohn Rumpelein27 Nov
  • Re: randomizing returned rowsPaul DuBois27 Nov