What is the best way to "shuffle" a database record table?

Question

Let's say that I have a table with a bunch of records that I want to randomly present to users. I also want users to be able to paginate back and forth, so I need to keep some order at least for a while.

The application is basically just AJAX and uses the cache for pages that have already been visited, so even if I always performed random results when the user tried to return, he would get the previous page because it would load from the local cache.

The problem is that if I return only random results, there may be some duplicates. Each page contains 6 results, so to prevent this, I will need to do something like WHERE id NOT IN (1,2,3,4 ...)where I would put all previously loaded identifiers.

The huge drawback of this solution is that it will not be possible to cache anything on the server side, as each user requests different data.

An alternative solution would be to create another column to organize the records and shuffle it into each insertion time block. The problem here is that I will need to set a random number from the sequence for each record in the table, which will require as many queries as there are records.

I use Rails and MySQL, if relevant.

Answer 1

Try the following:

mysql> create table t (i int);
mysql> insert into t values (1),(2),(3),(4),(5),(6);
mysql> select * from t order by rand(123) limit 2 offset 0;
+------+
| i    |
+------+
|    6 | 
|    4 | 
+------+
mysql> select * from t order by rand(123) limit 2 offset 2;
+------+
| i    |
+------+
|    2 | 
|    3 | 
+------+
mysql> select * from t order by rand(123) limit 2 offset 4;
+------+
| i    |
+------+
|    5 | 
|    1 | 
+------+

Note that the rand () function has an initial value of (123). Also note that if you repeat the last three queries, you will get the same result every time.

Answer 2

I would do the following (assuming a sequential, numeric primary key):

• Create a random number and save it in a user session.
• When the user views the data, the query for all rows
• Use the number stored in the session as a seed to generate the same "random" order of identifiers for each request.
• , .

Answer 3

" ", , - : ( Postgres, )

update mytable set sortorder = random() * 100000000;

select * from mytable order by sortorder, primarykeyid;

MAY , sort by primarykeyid .

, . , , , . .

, , , , . , "", , ( ).

The extent to which the motivation for presenting random data is how well it will work. It also depends on the amount of data, etc.

But this is a cache-friendly way to clear this if it matters to you. It also has a stateless person (no session information is required).