Re: WHERE problem

On Tue, February 20, 2007 10:46 am, Németh Zoltán wrote: > 2007. 02. 20, kedd keltezéssel 11.39-kor > tg-php@gryffyndevelopment.com > ezt írta: >> Different strokes for different folks... >> >> Might I toss a new recommendation into the mix? >> >> SELECT text FROM fortunes ORDER BY RAND() LIMIT 1; >> > > that's not new :) > a couple of people recommended it earlier today/yesterday > > this is perfect, but only if the table is not very large. > see > > http://www.titov.net/2005/09/21/do-not-use-order-by-rand-or-how-to-get-random-rows-from-table/Û p4È > ²D?
3½² Another option for extremely large tables, is to ADD a field, say, 'random_cache' of type float, and index that field. You can then: SELECT id FROM whatever ORDER BY random_cache LIMIT $limit; Gather your id's together, and then: UPDATE whatever SET random_cache = random() WHERE id in ($ids) You'd have an index on id as well, of course. So, in essence, as you "use up" random rows, you re-assign those rows with a new random number, and toss them back in the "pile" It *does* re-shape the index on the random index, so if that gets too lop-sided and you are selecting a large $limit, the DB takesa beating, but this is still pretty efficient for what most people are trying to do most of the time. It puts the heavy lifting into a DB index, which is about as efficient as you're going to get. It is possible for two users to get the same "random" selection, if their queries inter-twine. You could wrap the whole thing in a transaction, possibly, if that's undesirable. I use this for a playlist of ~30 songs every day out of ~60000 rows, and it works well. But I only do the queries once a day, and store the result, so maybe it won't scale well for heavily-trafficed site. I'd be interested in hearing anybody who benchmarks this compared to other methods, but confess I'm not in enough of a performance bind to feel the need to benchmark for myself. Though I know for sure it beat the ORDER BY random() on my usage, as that's what I had and it was killing me. -- Some people have a "gift" link here. Know what I want? I want you to buy a CD from some starving artist. http://cdbaby.com/browse/from/lynch Yeah, I get a buck. So?