What's the best way to "shuffle" a table of database records?

Question

Say that I have a table with a bunch of records, which I want to randomly present to users. I also want users to be able to paginate back and forth, so I have to perserve some sort of order, at least for a while.

The application is basically only AJAX and it uses cache for already visited pages, so even if I always served random results, when the user tries to go back, he will get the previous page, because it will load from the local cache.

The problem is, that if I return only random results, there might be some duplicates. Each page contains 6 results, so to prevent this, I'd have to do something like WHERE id NOT IN (1,2,3,4 ...) where I'd put all the previously loaded IDs.

Huge downside of that solution is that it won't be possible to cache anything on the server side, as every user will request different data.

Alternate solution might be to create another column for ordering the records, and shuffle it every insert time unit here. The problem here is, I'd need to set random number out of a sequence to every record in the table, which would take as many queries as there are records.

I'm using Rails and MySQL if that's of any relevance.

Answer 1

Try this:

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 a seed value (123). Note also that if you repeat the last three queries you'll get the same result every time.