PostgreSQL problem with RAND()
AlexisWilke - November 24, 2008 - 04:54
| Project: | Quotes |
| Version: | 6.x-1.x-dev |
| Component: | Code |
| Category: | bug report |
| Priority: | critical |
| Assigned: | AlexisWilke |
| Status: | closed |
Jump to:
Description
The query generated whenever you want to select a random row uses DISTINCT(n.nid) and that does not work with BY ORDER RAND(). I tried a few things and could not find a way to make the DISTINCT work. Since we have a limit of 1 anyway, I would suggest we remove the DISTINCT() altogether. It should pretty much achieve the same effect, right? How many times can you really get the same node in the quotes table?
Thank you.
Alexis Wilke
| Attachment | Size |
|---|---|
| quotes-6.x-1.x-random.patch | 454 bytes |
#1
Also, you may want to read this thread:
http://www.phpbuilder.com/board/showthread.php?t=10338930
about using ORDER BY RAND().
Thank you
Alexis Wilke
#2
Thanks, Alexis. That is an interesting article. I can't imagine any one having enough quotes to make it a serious problem, but I might look at the code to see if there might be some improvements anyway.
I tested and improved upon your patch. Actually, there might be ways to get the same node more than once. But yes, with a LIMIT 1, what difference does it make?
#3
Nancy,
You're welcome.
One way to make the number of quotes limited would be to include the date whenever they are added, and delete old once. You could do something like: if count(*) > 1000, then delete oldest until count(*) <= 1000. But indeed, I do not think that's useful until someone complains about it.
Another way to have a random is to know all the IDs, if they are all available, then a call to random in PHP and then SELECT on the ID would be a lot faster (Assuming the ID is in an index).
And just rolling is enough on a busy site. So simply showing the next entry could be more than enough. In that regard, we could have an option so admins can choose that specific method.
Thank you.
Alexis Wilke
#4
That is an interesting concept, Alexis. Perhaps you could open an new, low priority feature request for it (you could even mark it postponed to see if anyone else comments on it).
#5
Automatically closed -- issue fixed for two weeks with no activity.