:: Personal website of a Toronto web designer
Lately » MySQL RAND()
Korax, my hosting company has updated MySQL and a few other things recently. There is one thing I noticed. I might be wrong, but selecting a random row via following does not work in the new version of MySQL?!
SELECT * FROM your_table ORDER BY RAND() LIMIT 1;
However:
SELECT * FROM your_table ORDER BY RAND(NOW()) LIMIT 1;
fixed the thing, but I am not happy with the random results.
A comment at mysql.com shed some light on this issue.
:: Sasha, at 11:31 pm on Monday, 03. February 2003
It's working for me :-)
you might try RAND(UNIX_TIMESTAMP())
:: Davor, at 12:58 am on Tuesday, 04. February 2003
Is RAND() working for you? We are probably on a different server.
I am talking about:
http://www.sashadesign.com/pic/random/
It was working perfectly until the MySQL change. I included UNIX_TIMESTAMP instead, but I think it is still not random as it is supposed to be.
:: Sasha, at 01:33 am on Tuesday, 04. February 2003
I don't see your database name in the list :-)
:: Davor, at 08:31 am on Tuesday, 04. February 2003
I got canadian flag picture almost 10x in a row. Random enough for me ;o)
:: vitez-koja, at 09:07 am on Tuesday, 04. February 2003
Sigh... at least you can use RAND() ... My host still has an older version of MySQL where any implementation of RAND() doesn't work at all, it seems. I have to create a seed and do an srand() in PHP, then pass that random number along in the MySQL query.
On the other hand, a different site I run on another server has a new version of MySQL where RAND() works just fine, and is so much more elegant, code-wise.
:: Bob, at 10:08 am on Tuesday, 04. February 2003
Koja, that's because you love your country :-)
:: Sasha, at 11:13 am on Tuesday, 04. February 2003
Hej Sasha - you made me think about that one for a second: "I am not happy about the random results." :-)
:: Chriztian Steinmeier, at 04:31 pm on Tuesday, 04. February 2003
Hah, my random pic was Neighbour's cat, not the flag :-)
http://www.sashadesign.com/pic/1/
:: Sasha, at 07:21 pm on Tuesday, 04. February 2003
Back to the topic. I don't know what went wrong with the MySQL RAND() function but I think I found the solution:
http://www.php.net/manual/en/function.rand.php#26418
The code is a bit longer, but it is more efficient with large tables.
:: Sasha, at 08:05 pm on Tuesday, 04. February 2003
Hey guys my host upgraded the server recently and was having a problem with ORDER BY RAND() I have been searching for awhile.. but I found the solution that works for me.
These useded to work but do not now.
ORDER BY RAND() LIMIT 1
ORDER BY RAND(NOW()) LIMIT 1
But these seems to work for me
ORDER BY rand(" . time() . " * " . time() . ") LIMIT 1
Here is the page I found it on..
I just thought I would help you guys on this while I was randomly searching on google.com
:: David Eaton, at 02:39 pm on Wednesday, 23. April 2003
I use this with success
SELECT banner_id from banner_table ORDER BY banner_weight*SUBSTRING(RAND(),6,2)
this will return a random ordered list, the problem seam to be that mySQL produce the random number depending on the microtime spend on eatch fetch row, so when mulitple rows are fetched, they will almost always be fetched in the same order and therefore they will have assigned the random number in same order (the number will though change from query to query, but not the order, only if some sort of encryption is used - like the SUBSTRING)
some of my users did though report that the SUBSTRING was not effivent on there server - then I have told them to use MD5 encryption instead witch did solve there problems
:: Niels Chr., at 06:31 pm on Wednesday, 14. May 2003
David Eaton--my site refreshes a randomized picturebar written in PHP and MySQL. For months ORDER BY RAND(NOW()) worked just fine for selecting random pictures from the database... but then one day I noticed it kept selecting one of the pictures over and over. Quite mysterious that it would go from working to broken spontaneously. Your solution fixed the problem. So thanks.
:: Robert K S, at 11:43 pm on Wednesday, 14. January 2004
I have made test and it works fine ... 10 numbers equally provided over time and sequences are fine
:: zoka, at 07:29 pm on Tuesday, 06. July 2004
thanks a lot for the info the NOW() was working too well for this game I'm creating. BTW, about how many records does a table have to have in order for the RAND function to work efficiently?
:: Agustin, at 10:32 am on Monday, 25. April 2005
Better do not user ORDER BY RAND():
http://www.titov.net/2005/09/21/do-not-...rom-table/
:: Anton, at 09:46 am on Wednesday, 28. September 2005
From http://www.php.net/manual/en/function.rand.php#26418 :
ORDER BY RAND() works for small tables, but once the tables grow larger than 300,000 records or so this will be very slow because MySQL will have to process ALL the entries from the table, order them randomly and then return the first row of the ordered result, and this sorting takes long time.
:: Anonymous, at 01:14 am on Tuesday, 20. June 2006
I have a table with banners. Those banners have percentages (where I decide which banner is showed more often). I would like to select a banner randomly taking in consideration the "show"-percentages.
Does anybody know how to do this?
:: GFOne, at 03:00 pm on Wednesday, 13. September 2006
I want to update a field with random values that having rang 0 to 1000. How i can do this?
:: SEO Specilist, at 11:57 pm on Friday, 05. October 2007
Useless scrawling facts:
Top 5 scrawlers:
(updated once a day)
556 Sasha
488 zoka
316 vitez-koja
235 Davor
58 mungos
