-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
Magnus Manske writes:
http://jan.kneschke.de/projects/mysql/order-by-rand/
That method only requires the primary key being an integer (which is true for the page table), nothing else. The guy who wrote the page tested it on a table with 1.000.000 rows, 1.000 "random" queries took ~0.6 seconds. Sounds like it would be fast enough for us, and we could get rid of that page_random field altogether.
Um, no, it is faster than a pure ORDER BY RANDOM (almost anything is!), but not as fast as our current system, which is an excellent one. His method also only works well if you have no holes: his method of finding a value if the table has holes is not truly random.
Jens Frank asked:
Is this MySQL-specific? Is it possible to do this with PostgreSQL, too?
It's certainly possible in Postgres, but I'd not recommend it, for the reasons mentioned above. I also wrote a blog post on this exact topic about two months ago:
http://people.planetpostgresql.org/greg/index.php?/archives/40-Getting-rando... or http://makeashorterlink.com/?K5D25287D
- -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200607261611 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8