Random query, how to keep sort order locked?

[PeterNRG]

PHP Version 5.1.6 - mySQL 5.0.22

Let's pretend we have 100.000 rows in a table (all containing images with details), and I want to display 100 images at a single frontend page, but randomly retrieved and keeping the sort order once initiated to a user session.

With the normal RAND function, you have a chance to stumble on the same images when you navigate to the next page with again 100 items on. But even worse, going back to page 1, will return totally different results, and will freak out the viewer :)

How to get a (fake) random listing on a fairly large database, where every requested row is unique, and the sort order is being locked (until you press a randomize button ie, or start a new session) while keeping speed in mind (so trying to avoid reading out all rows up front).

[orban]

If the 100.000 are static, shouldn't be too hard.

Write a function to randomly apply the numbers 1 to 1000 to all rows so you have every number exactly 100 times.

Upon creating a new session create a permutation of all numbers from 1 to 1000 and store it (or cut it off after m items if you just need m pages). The i_th item of the permutation is i_th page (with all rows having random value i)

If it's dynamic...I don't really know apart from reassigning every day or so.

On a second thought:

X rows, k items per page, with random numbers 1 to ceil(x/k), |Rows with ceil(x/k)| < k

When adding a new row give it a 1/k chance to get the random number j (1 <= j <= k). Then give a random item with random value j the value k and the new item value j

if x%k=0 give it a 1/(k+1) chance to get random number 1, 2, ..., k+1 (and swap like above)

I don't think I can proof this will actually be random but will be quite close I guess.

edit2: actually, if you want true permutations of all 100.000.....I don't think there's an easy way. the outlined solution here only gives you permutations of the pages :(

[PeterNRG]

If the 100.000 are static, shouldn't be too hard.

Write a function to randomly apply the numbers 1 to 1000 to all rows so you have every number exactly 100 times.

Thanks for the idea's so far. The 100.000 is actually a growing list of images, with the possibility of some that get deleted after a while.

[AlexMack]

there's a much easier way to do it. use a seed.

http://dev.mysql.com/doc/refman/5.1/en/mathematical-functions.html#function_rand

rand(seed) will always return the same set of results in the same order. a different seed will have its own unique set.