ORDER BY RAND() same every time 1

[OsakaWebbie]

I'm trying to get a random record from a table, but the overwhelmingly recommended method - "SELECT * FROM table ORDER BY RAND() LIMIT 1" - gives me the same record every time. When I try the query without the LIMIT clause, I can see that the records are indeed in an order that cannot be explained as anything but random (i.e. not the order I entered them, and not the order of the primary key), but when I run the query again they repeatedly show up in the same "random" order. Is there something I need to do to seed it so that my results change? I see no mention of this anywhere - everyone seems to act as if this query will simply work.

 

[mbrooks]

This is due to a bug in MySQL. This has been resolved with versions 4+.

M. Brooks

http://mbrooks.info

 

[OsakaWebbie]

The server is running 4.0.25. Also, if that isn't new enough, I can't upgrade, because it's a hosting service. What should I do?

 

[TonyGroves]

Before using RAND(), you need to seed the random-number generator. You can do this by calling RAND() with a varying value as an argument, for example RAND(CURDATE()+CURTIME()) . Since this would be different each time, you would get a different sequence of "random" numbers.

 

[TonyGroves]

Just to clarify that last post, you would first issue a dummy query like "SELECT RAND(CURDATE()+CURTIME())" and then issue your actual query using RAND() without an argument.

 

[OsakaWebbie]

Thanks! I sort of suspected a seed was involved (I even hinted that in my first post), but saw no mention of it in any of the web pages I found that talked about doing this.

Interestingly, after doing the seed just once, I was able do my query multiple times (without doing the seed again) and get different results - it was as if it just needed a kickstart. But I don't know how long it would continue to work before "going to sleep" again, so I did include a seed query in my code before the data query, just to be on the safe side.

 

[imstillatwork]

does the rand() limit 1 technique work well on large-ish tables? like 10K-50K rows, or is there performance issues?

Just seems too easy! I use it on small stuff, but never have needed to onlarger tables.

Kevin

http://www.lot-o-nothin.com/cfml/

 

[TonyGroves]

Since the RAND() value for any record is unpredictable (unless you're using the same seed value as before), all the records would have to be read and RAND()ed then sorted, so it looks like performance on big tables would be poor. However, it's not beyond the bounds of possibility that the MySQL optimiser might have a special routine for this case, where it would simply pick 1 record at random.

 

[OsakaWebbie]

I have read of people who were indeed finding the ORDER BY RAND() slow on large tables. If I had a big table (which my current one isn't), I would probably see if there is a way to get the total number of records in the table and multiple that by the result of RAND() in the LIMIT clause (something like "LIMIT COUNT(*)*RAND(),1") to get only a single record. I don't know if that is possible in SQL within one query, but my mind thinks it would be worth a try.

 

[imstillatwork]

So with COUNT(), the DB pretty much 'knows' the answer, and does not need to scan the table, right?

I've been writing queries for 5 or 7 years, but have only recently gotten into some more advanced things like performance issues and compex functions and aggregates, etc.



Kevin

http://www.lot-o-nothin.com/cfml/

 

[TonyGroves]

Unfortunately LIMIT only accepts constant values, so you would have to do 2 queries - one to get the count, another to get the record. But it sounds like a good option which could well be faster for a big table.