Rails—get a random record from db?

Question

To get a single random record from the db, I'm currently doing:

User.all.sample

But when there are 100000+ users, it takes a few seconds to load them all, just to select one.

What's the simplest way to get load a single random user from db?

Answer 1

You can try following database independent query:

User.find(User.pluck(:id).sample)
[DEBUG]  (36.5ms)  SELECT `users`.`id` FROM `users`
[DEBUG] User Load (0.5ms)  SELECT  `users`.* FROM `users` WHERE `users`.`id` = 58229 LIMIT 1

this one fires two queries but this one is performance efficient as it took only 37ms to get single random user record.

whereas the following query will take around 624.7ms

User.order("RAND()").first
[DEBUG] User Load (624.7ms)  SELECT  `users`.* FROM `users`  ORDER BY RAND() LIMIT 1

I have checked this for 105510 user records.

Answer 2

Using Postgresql or SQLite, using RANDOM():

User.order("RANDOM()").first

Presumably the same would work for MySQL with RAND()

User.order("RAND()").first

Answer 3

Well after lot of trials and errors i've found this solution to be helpful and error free.

Model.find(Model.ids.sample)

Model.ids will return an array of all ids in the database. we then call sample method on that array that will return a random item in the list.

Answer 4

You can find the maximum user id in the table and find a user given a random id limited to this maximum. Example:

max_id = User.order(id: :desc).limit(1).pluck(:id).first
user = User.find_by('id > ?', rand(max_id))

These two queries are extremely fast because you are using the index for the primary key (id).

Answer 5

for rails 6

you can pass records count to get how many records you want

User.all.sample(1)

Above Query will Return only one random record of the user

Answer 6

You can get a sample in Rails console using Model.all.sample.attribute.

E.g.:

Contact.all.sample.name
=> "Bob Mcmillan"