title: What's the best way to "shuffle" a table of database records?
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>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 <code>WHERE id NOT IN (1,2,3,4 ...)</code> where I'd put all the previously loaded IDs.</p>
  
  <p>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.</p>
  
  <p>Alternate solution might be to create another column for ordering the records, and <em>shuffle</em> it every <em>insert time unit here</em>. 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.</p>
  
  <p>I'm using Rails and MySQL if that's of any relevance.</p>