Stable / repeatable random sorting (MySQL, Rails)

Question

I would like to break the pages into a randomly sorted list of ActiveRecord models (rows from a MySQL database).

However, this randomization should be maintained based on the session, so that other people who visit the website also get a random list of records with the possibility of pagination.

Suppose there are enough objects (tens of thousands) that store randomly sorted identification values ​​in a session or in a cookie too large, so I have to temporarily store it in some other way (MySQL, file, etc.).

Initially, I thought I could create a function based on the session identifier and the page identifier (returning the object identifiers for this page), however, since the object identifier values ​​in MySQL are not sequential (there are spaces) that seemed to fall as I poked it. The best part is that this does not require minimal storage, but the disadvantages are that it is probably quite difficult to implement and probably an intensive processor.

My feeling is that I have to create an intersection table, something like:

random_sorts( sort_id, created_at, user_id NULL if guest) random_sort_items( sort_id, item_id, position ) 

And then just save the 'sort_id' in the session. Then I can paginate random_sorts WHERE sort_id = n ORDER BY position LIMIT ... as usual.

Of course, I would have to put some reaper in there to remove them after some period of inactivity (based on random_sorts.created_at).

Unfortunately, I would have to cancel the sorting as new objects are created (and / or old objects deleted, although deletion is very rare). And when the load increases, the size / performance of this table (even correctly indexed) falls.

It seems like this should be a resolute issue, but I can't find any rails plugins that do this ... Any ideas? Thanks!!

Answer 1

MySQL has a RAND function that you can use in your ORDER clause, passing in a seed bound to a user session.

ORDER BY RAND (?)

Where? is the initial value from the session. This will give you the ability to re-arrange for all queries.

Answer 2

I probably missed something, but it doesn't look like it

select ... order by sha1(concat($session_id,item_id)) limit m,n;

work to give you an ordered list of duplicate lists in each session? It is not very nice to use an index, but you avoid tables / invalidating pre-fill / tmp.

Answer 3

Personally, to save space and reasonableness, I just used random seed using your user_id.

 srand user_id items.sort_by{ rand }