Why does MySQL Rand() hate me?

Question

Here is a simplified query of something I am trying to do on a larger join query. It is still breaking on this small scale. I am trying to generate a random number for each row pulled back in the range of 1-60. I then want to order the returned rows by this random number.

 SELECT downloads . * , 
(FLOOR( 1 + ( RAND( ) *60 ) )) AS randomtimer
FROM downloads
ORDER BY randomtimer
LIMIT 25

I have 2 databases I have tried this query on. A live one and a dev one. I have side by side compared the two and they are both structurally the same. It works correctly on the dev one. returning the rows ordered by the randomtimer.

The live table returns all 1's in the randomtimer column. If I order by randomtimer ASC they become all 60s. If I remove randomtimer from the Order By Clause it returns correct individual values. So something is tweaking the values on the ORDER BY statment.

Anyone have any ideas on this? Might I be overlooking something? WTF? WTF?

Answer 1

From the RAND docs:

You cannot use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times. However, you can retrieve rows in random order like this:

mysql> SELECT * FROM tbl_name ORDER BY RAND();

I'd guess the variance is due to different MySQL version, query plans or table data but don't know which.

Answer 2

Aside from what mr. unknown has said, there's another issue.

You are generating a random number between 1 and 60 then selecting the top 25 rows. If there are enough rows that you would (statistically) end up with more than 25 with a random value of 1, then the first 25 rows would of course all have a value of 1 in the "randomtimer" column.

So this is likely due to the fact that you just have a lot more data in production than on the dev server.

Answer 3

I decided to scrap that idea and make an array of random numbers in php the same length as the returned results and just sort and use that.

Answer 4

I'll throw out an idea ... the RAND function is using the time as its seed. On the live system, the entire query is finishing within the same millisecond, therefore all the random numbers are the same. On the dev system, it is taking longer so you get more random numbers. Might make sense if your live system is more powerful than your dev system.

Just a thought.