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

So an easier approach would be passing seed value along with the page and limit value. In the API call when you pass the page and limit parameter along with that pass the seed parameter for subsequent API calls.

So lets say for the first time we pass page and limit parameter in the API call. In the backend we assign a seed value at random,

// Use the seed value if provided or generate a random seed
    const usedSeed = seed || Math.floor(Math.random() * 100000); // Change the range as needed.

and in the response we pass the seed value along with that. So for subsequent call we test if the seed value is there in the API call then we pass the same seed value in the SQL select query, thus this will randomize the responses and paginate too in the respective order.

const [rows] = await connection.execute(
      `SELECT * FROM generated_results WHERE id IN (${placeholders}) ORDER BY RAND(${usedSeed})`,
      Ids
    );

Answer 2

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 3

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.

Answer 4

The combination of

1. random ordering
2. pagination
3. HTTP (stateless)

is as ugly as it comes: 1. and 2. together need some sort of "persistent randomness", while 3. makes this harder to achieve. On top of this 1. is not a job a RDBMS is optimized to do.

My suggestion depends on how big your dataset is:

Few rows (ca. <1K):

• select all PK values in first query (first page)
• shuffle these in PHP
• store shuffled list in session
• for each page call select the data according to the stored PKs

Many rows (10K+):

This assumes, you have an AUTO_INCREMENT unique key called ID with a manageable number of holes. Use a amintenace script if needed (high delete ratio)

• Use a shuffling function that is parameterized with e.g. the session ID to create a function rand_id(continuous_id)
• If you need e.g. the records 100,000 to 100,009 calculate $a=array(rand_id(100,000), rand_id(100,001), ... rand_id(100,009));
• $a=implode(',',$a);
• $sql="SELECT foo FROM bar WHERE ID IN($a) ORDER BY FIELD(ID,$a)";
• To take care of the holes in your ID select a few records too many (and throw away the exess), looping on too few records selected.

Answer 5

I would have your PHP generate your random record numbers or rows to retrieve, pass those to your query, and save a cookie on the user's client indicating what records they've already seen.

There's no reason for that user specific data to live on the server (unless you're tracking it, but it's random anyway so who cares).

Answer 6

First you should stop using the ORDER BY RAND syntax. This will bad for performance in large set of rows.

You need to manually determine the LIMIT constraints. If you still want to use the random results and you don't want users to see the same results on next page the only way is to save all the result for this search session in database and manipulate this information when user navigate to next page.

The next thing in web design you should understand - using any random data blocks on your site is very, very, very bad for users visual perception.

Answer 7

You have several problems to deal with! I recommend that you go step by step.

First issue: ^{results they already seen not to appear again}

1. Every item returned, store it in one array. ^{(assuming the index id on the example)}
2. When the user goes to the next page, pass to the query the NOT IN:

MySQL Query

SELECT * FROM table WHERE id NOT IN (1, 14, 25, 645) ORDER BY RAND() LIMIT 0,10;

What this does is to match all id that are not 1, 14, 25 or 645.

---

As far as the performance issue goes: ^{in a memory efficient way}

SELECT RAND( )
FROM table
WHERE id NOT
IN ( 1, 14, 25, 645 )
LIMIT 0 , 10

Showing rows 0 - 9 (10 total, Query took 0.0004 sec)

AND

SELECT *
FROM table
WHERE id NOT
IN ( 1, 14, 25, 645 )
ORDER BY RAND( )
LIMIT 0 , 10

Showing rows 0 - 9 (10 total, Query took 0.0609 sec)

So, don't use ORDER BY RAND(), preferably use SELECT RAND().