CODEWITHSUNDEEP
mysqlrandom
kevzettler
kevzettler

Reputation: 5213

Why does MySQL Rand() hate me?

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?

Upvotes: 2

Views: 562

Answers (4)

Rap
Rap

Reputation: 7292

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.

Upvotes: 0

kevzettler
kevzettler

Reputation: 5213

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.

Upvotes: 0

Eric Petroelje
Eric Petroelje

Reputation: 60569

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.

Upvotes: 7

brian-brazil
brian-brazil

Reputation: 34192

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.

Upvotes: 6

Related Questions