CODEWITHSUNDEEP
ruby-on-railsrubyruby-on-rails-3
Matt Fordham
Matt Fordham

Reputation: 3187

How to randomize (and paginate) large set of results?

I am creating a contest application that requires the main index page of entries to be randomized. As it will potentially be a large set of entries (maybe up to 5000), I will also need to paginate them.

Here are the challenges:

  1. I have read that using a database's 'random()' function on a large set can perform poorly.
  2. I would like for things to not be re-randomized when the pagination links are clicked. In other words, it should return a random set upon first load and then keep the same order while someone uses the pagination.

The second challenge seems potentially unrealistic, but perhaps there are some create solutions out there?

Thanks for any input.

Upvotes: 0

Views: 317

Answers (2)

Xiaohui Zhang
Xiaohui Zhang

Reputation: 1125

a simple way I suggest is writing your own random function with SQL query, for the function more complicated the more random, for example:

you already know

select * from your_table order by rand() limit 0, 10

assume your_table has a primary key "id", now replace "rand()" with "MOD(id, 13)"

select * from your_table order by MOD(id, 13) limit 0,10

if your_table has a datetime column, the result would be better, try this query:

select * from your_table order by MOD(id, 13), updated_at limit 0,10

also if you don't think it's not random enough, there is I bet you love it:

select * from your_table order by MD5(id) limit 0, 10

Upvotes: 1

Max
Max

Reputation: 15955

I would just use a random number generator to select IDs, and store the seed in the session so a user will see the same ordering while paginating. I would probably also use a hash to make sure each ID is picked only once.

Upvotes: 0

Related Questions