PHP MySQL Pagination with Random Ordering

PHP MySQL pagination with random ordering

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.

MySQL Pagination with Randomness

1. Filter 1M rows to find 100K rows -- a full table scan of 1M rows
2. Pick, say, 10 random rows from the 100K -- a scan of 100K rows, unless you use some technique from http://mysql.rjweb.org/doc.php/random .
3. Leave behind something that lets you "paginate". Do you care if it accidentally repeats rows? Is this web-based, meaning that "leaving something behind" can be messy -- such as listing all the items already seen when in the url of the [Next] button.
4. Pagination via LIMIT and OFFSET is Order(N*N) to walk through all N pages. 100K rows, 10 rows at a time, would be something like 500M. Even worse than the original 1M.

Now, let me invent a solution that solves the cost of both "random" and "pagination". See http://mysql.rjweb.org/doc.php/random#case_extra_float_column_for_randomizing

1. Prep: Add an extra column with some random number in it. Index it. This will become both the randomizer and the "where you left off" to avoid using the costly OFFSET.
2. See the link for one approach to getting the first 10 random rows.
3. See http://mysql.rjweb.org/doc.php/pagination for more on "remembering where you left off".

Do all those, and the cost becomes mostly the original Step 1, namely whatever it takes to filter the 1M rows. Hopefully, it won't even be that costly -- this depends on whether a suitable index can be had that gets all the way to the LIMIT 10. (My gut says that won't be practical.)

Summing up:

SELECT ...
    FROM ...
    WHERE filters...
       AND rnd > $left_off
    ORDER BY rnd
    LIMIT 10;

If you start $left_off at 0, you get a repeatable, but seemingly random, sequence. And the [Next] button only needs to pass the last value of rnd that was fetched from the current page.

Now to make it not repeatable? Re-populate rnd every night. (That would mess with anyone in the process of paginating.)

How to: MySQL order by user_id (RAND) with pagination

The RAND() function does not really generate random numbers but what's called pseudo random numbers: numbers are calculated with a deterministic formula and they're just intended to look random. To calculate a new number, you take the previous one and apply the formula to it, and that's how we get different output with a deterministic function: by using different input.

The initial number we use is known as seed. If you have a look at the manual you'll see that RAND() has an optional argument:

RAND(), RAND(N)

Returns a random floating-point value v in the range 0 <= v < 1.0. If
a constant integer argument N is specified, it is used as the seed
value, which produces a repeatable sequence of column values

You've probably figured out by now where I want to go:

mysql> SELECT language_id, name FROM language ORDER BY RAND(33);
+-------------+----------+
| language_id | name     |
+-------------+----------+
|           3 | Japanese |
|           1 | English  |
|           4 | Mandarin |
|           6 | German   |
|           5 | French   |
|           2 | Italian  |
+-------------+----------+
6 rows in set (0.00 sec)

mysql> SELECT language_id, name FROM language ORDER BY RAND(33);
+-------------+----------+
| language_id | name     |
+-------------+----------+
|           3 | Japanese |
|           1 | English  |
|           4 | Mandarin |
|           6 | German   |
|           5 | French   |
|           2 | Italian  |
+-------------+----------+
6 rows in set (0.00 sec)

P.S. The manual is not explicit about the seed range (it just says integer), you might need some extra research (or just some quick testing).

Random Result Pagination

Mysql RAND() accepts a seed as an optional argument. Using a seed, it will return the same randomized result set each time.

What you could do is generate a random seed in PHP on the first page request and pass it along to each page using a query string.

Edit: Sorry, I didn't realize the solution was posted already but was deleted.

Random values in MySql query with pagination

You should use a seed for the MySQL RAND to get consistent results. In PHP you do a

$paginationRandSeed = $_GET['paginationRandSeed']?
    ( (int) $_GET['paginationRandSeed'] ):
    rand()
;

and in MySQL you use that seed

"SELECT * FROM table ORDER BY RAND(".$paginationRandSeed.") LIMIT 0,20"

Of course you'll need to propagate the initial seed in the page requests.

Good luck,

Alin

---

Related Topics