PHP MySQL pagination with random ordering

Question

This is a problem with a ordering search results on my website,

When a search is made, random results appear on the content page, this page includes pagination too. I user following as my SQL query.

SELECT * FROM table ORDER BY RAND() LIMIT 0,10;

so my questions are

1. I need to make sure that everytime user visits the next page, results they already seen not to appear again (exclude them in the next query, in a memory efficient way but still order by rand() )

2. everytime the visitor goes to the 1st page there is a different sets of results, Is it possible to use pagination with this, or will the ordering always be random.

3. I can use seed in the MYSQL, however i am not sure how to use that practically ..

Answer 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.

Answer 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.