15

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?

Mirror318
  • 10,810
  • 11
  • 57
  • 93

6 Answers6

28

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.

Ganesh
  • 1,816
  • 1
  • 17
  • 31
  • 2
    `User.find(User.pluck(:id).sample)` is great, not DB-type dependent. If anyone needs more than one record, you can add an argument to sample, like `User.find(User.pluck(:id).sample(4))` – Mirror318 Mar 28 '18 at 05:49
  • 2
    I don't recommend any of this. The first solution will consume more and more memory depending on users count. The second solution can be really slow and also consume significant resources. There many alternatives depending on what kind of random you **really** need. Actually I recommend to avoid randomization as much as possible. – Alex Tatarnikov Mar 28 '18 at 09:26
  • how first solution will consumes more memory? – Ganesh Mar 28 '18 at 09:34
  • 1
    what are other efficient alternatives. please post them as answer so others will also came to know the solution. – Ganesh Mar 28 '18 at 09:35
  • 2
    @AlexTatarnikov I need a random record from the db, no special kind of random, just a random record. If you have a more efficient solution please share – Mirror318 Mar 28 '18 at 20:51
  • 2
    @GaneshNavale You will load array with all ids to memory. Imagine you have thousands of hundreds ids. – Alex Tatarnikov Mar 28 '18 at 20:55
  • @Mirror318 This is one of them https://stackoverflow.com/a/5297523/3702804 – Alex Tatarnikov Mar 28 '18 at 20:58
  • @AlexTatarnikov Offset is mentioned in another comment, you should post it as an answer here – Mirror318 Mar 28 '18 at 23:25
  • offset mention in another comment will be less efficient, and will take approx **565 ms** – Ganesh Mar 29 '18 at 05:52
  • `User.offset(rand(User.count)).limit(1).take` How does this work for you guys? For me, it took less than 10ms whereas others were taking around 80ms. – arunt May 23 '19 at 09:07
  • Check the first solution on a table containing 25m of records (well, I did, this does not work). The second one works but it takes some time. Fully agree with @AlexTatarnikov – Nick Roz Jan 26 '21 at 15:46
10

Using Postgresql or SQLite, using RANDOM():

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

Presumably the same would work for MySQL with RAND()

User.order("RAND()").first
fongfan999
  • 2,456
  • 1
  • 11
  • 21
6

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.

Osama Abdullah
  • 2,322
  • 2
  • 10
  • 19
3

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).

Renato Silva Das Neves
  • 241
  • 3
  • 6
2

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

Darshan Thakor
  • 89
  • 5
1

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

E.g.:

Contact.all.sample.name
=> "Bob Mcmillan"
Raphael Onofre
  • 194
  • 3
  • 13