PHP MySQL pagination with random ordering

Solution:1

Use RAND(SEED). Quoting docs: “If a constant integer argument N is specified, it is used as the seed value.” (http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_rand).

In the example above the result order is rand, but it is always the same. You can just change the seed to get a new order.

SELECT * FROM your_table ORDER BY RAND(351);

You can change the seed every time the user hits the first results page and store it in the user session.

Solution:2

Random ordering in MySQL is as sticky a problem as they come. In the past, I’ve usually chosen to go around the problem whenever possible. Typically, a user won’t ever come back to a set of pages like this more than once or twice. So this gives you the opportunity to avoid all of the various disgusting implementations of random order in favor of a couple simple, but not quite 100% random solutions.

Solution 1

Pick from a number of existing columns that already indexed for being sorted on. This can include created on, modified timestamps, or any other column you may sort by. When a user first comes to the site, have these handy in an array, pick one at random, and then randomly pick ASC or DESC.

In your case, every time a user comes back to page 1, pick something new, store it in session. Every subsequent page, you can use that sort to generate a consistent set of paging.

Solution 2

You could have an additional column that stores a random number for sorting. It should be indexed, obviously. Periodically, run the following query;

UPDATE table SET rand_col = RAND();

This may not work for your specs, as you seem to require every user to see something different every time they hit page 1.