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
