Alex Teslik wrote:
Hello,
I'm working on a project that requires random images to be chosen from a
live database for a dynamic homepage. I found this link from Dr. Hipp that
details a very fast approach to selecting random rows:
http://www.mail-archive.com/sqlite-users@sqlite.org/msg14652.html
Unfortunately, it heavily weights the distribution of the randomly chosen
rows - to the extreme that the feeling of randomness is almost lost due to the
same images appearing so frequently.
After some research I concluded that it must be caused by breaks in the
ROWID values. However, after VACUUMing the database to re-order the ROWIDs I
find I am still having the same problem - weighted distribution.
Is there a way I can modify my query to attain a much more equal
distribution? It doesn't have to be perfect, but right now it is too
noticiably weighted.
After running a simple test, I confirmed a suspicion. VACUUM doesn't
reorder the ROWIDs, so you still have breaks.
Two suggestions:
1. If the image data are fairly stable, copy the table to a new table
so as to eliminate the breaks in the ROWIDs.
2. If the table is relatively small, load all the potential ids into
memory as a list and select randomly from the list.
Assuming the ROWIDs are actually sequential, without any breaks, it
would seem to me the distribution problem would lie in the pseudo-random
generator rather than in the query.
John
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
