How do you efficiently (in a DB independent manner) select random records from a table?

Question

This seems like an incredibly simple problem however it isn't working out as trivially as I'd expected.

I have a club which has club members and I'd like to pull out two members at random from a club.

Using RANDOM()

One way is to use random ordering:

club.members.find(:all, :order => 'RANDOM()').limit(2)

However that is different for SqLite (the dev database) and Postgres (production) since in MySql the command is RAND().

While I could start writing some wrappers around this I feel that the fact that it hasn't been done already and doesn't seem to be part of ActiveRecord tells me something and that RANDOM may not be the right way to go.

Pulling items out directly using their index

Another way of doing this is to pull the set in order but then select random records from it:

First off we need to generate a sequence of two unique indices corresponding to the members:

all_indices = 1..club.members.count
two_rand_indices = all_indices.to_a.shuffle.slice(0,2)

This gives an array with two indices guaranteed to be unique and random. We can use these indices to pull out our records

@user1, @user2 = Club.members.values_at(*two_rand_indices)

What's the best method?

While the second method is seems pretty nice, I also feel like I might be missing something and might have over complicated a simple problem. I'm clearly not the first person to have tackled this so what is the best, most SQL efficient route through it?

there is no truly DB independent method that is efficient. why do you need it to be DB independent ? — Mitch Wheat, Sep 01 '11 at 14:25
It really depends on the database and on how `random` you want your results to be. — Johan, Sep 01 '11 at 14:25
[This answer may be helpful](http://stackoverflow.com/questions/1244555/how-can-i-optimize-mysqls-order-by-rand-function). — Craig Stuntz, Sep 01 '11 at 14:34
@mitchwheat I am developing on SqLite and deploying on PosGres so need it to span both. I would also like to write DB independent code as much as possible as good practice. Johan - I'm only looking for reasonable randomness, doens't have to be truly random. Craig - ty — Peter Nixey, Sep 01 '11 at 14:39

score 1 · Accepted Answer · edited May 23 '17 at 11:55

The problem with your first method is that it sorts the whole table by an unindexable expression, just to take two rows. This does not scale well.

The problem with your second method is similar, if you have 10⁹ rows in your table, then you will generate a large array from to_a. That will take a lot of memory and time to shuffle it.

Also by using values_at aren't you assuming that there's a row for every primary key value from 1 to count, with no gaps? You shouldn't assume that.

What I'd recommend instead is:

Count the rows in the table.
```
c = Club.members.count
```
Pick two random numbers between 1 and the count.
```
r_a = 2.times.map{ 1+Random.rand(c) }
```
Query your table with limit and offset.
Don't use ORDER BY, just rely on the RDBMS's arbitrary ordering.
```
for r in r_a
    row = Club.members.limit(1).offset(r)
end
```

How do you efficiently (in a DB independent manner) select random records from a table?

3 Answers3