Yes, I saw that - and many others. One variation uses a LOCK to eliminate
the race potential, but I am reluctant to do that. Unfortunately, that has a
problem if there are key gaps. This looks a bit more fail-proof:
http://edrackham.com/featured/get-random-row-with-mysql-without-order-by-rand/
My issue with these techniques is that the vast majority of the users of the
Quotes module don't have anywhere near 12K quotes nodes (I personally have
163),
so do I let the one suffer, or let everyone use the "improved" technique
(meaning an additional table), or choose some point at which to switch
techniques (code bloat). And, of course, making sure it works on Postgres as
well.
I'd be happy to move this discussion to another venue if anyone is upset.
Nancy
Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L. King, Jr.
________________________________
From: Larry Garfield
Actually, I just found this in the MySQL manual in user comments:
SELECT FLOOR(RAND() * COUNT(*)) AS rand_row FROM {mytemp};
SELECT nid FROM {mytemp} LIMIT $rand_row, 1;
That could actually be faster, at the cost of being two queries so there are
technically potential race conditions. (Unlikely to be a problem in practice,
but still there.) I've not tried the above; I just saw it and thought it was
cool. :-) (It's also MySQL-specific. I don't know if the same thing would
work well on other databases.)
