MySQL provides a very simple method to select rows in random order. It uses the ORDER BY RAND()statement. If you will be used theORDER BY RAND() statement, MySQL will create a temporary table with all results, then will be assigned a random index to each row and finally will return the random rows! This makes the ORDER BY RAND() quite slow. In fact, some developer prefers alternative techniques, such as PHP random functions or special SQL select.

However, go ahead and let us see how to use ORDER BY RAND() in a pagination context. First of all, review this SQL statement:

SELECT * FROM `table` LIMIT amount OFFSET offset;

The select above will return amount rows  from offset without a specified order.

SELECT * FROM `table` ORDER BY RAND() LIMIT amount OFFSET offset;

The select above, instead, will return amount rows from offset in random order.  However, the select above doesn’t work in a pagination context because it will return different rows each time.

We can fix that by using a very simple trick. As said above, MySQL will create a temporary table when we use the ORDER BY RAND()statement. The random indexes will be created through a seed. Usually, this seed is null and MySQL will create it under the hood. We can set this seed with a custom own value:

SELECT * FROM `table` ORDER BY RAND(1234) LIMIT amount OFFSET offset;

This magical SQL statement will return a set of rows in random order by this random order will always be the same.

How to generate a random seed?

Now, you would like to find a method in order to create a different seed for each user session. We may use:

$ip = str_replace('.', '', $_SERVER['REMOTE_ADDR']);
$seed = ($ip + date('Hjn') );

As usual, please don’t hesitate to leave any questions or comments in the feed below, and I’ll aim to respond to each of them.