Goal: Get 3 random records from a database table.
There are a number or ways to pick random records in Rails, but none that are perfect.
Two problems with this approach:
The second problem is easy to fix (unless there are fewer than 3 things total, or our rand() takes too long to get 3 distinct numbers)
This solution isn’t great. And we didn’t solve the problem with fetching the whole set.
Ok, now we only select ids, so we don’t have to load the whole set. We could make the first query faster if we wrote SQL instead of fetching them as Things. (Also, does SQLite support the ‘IN’ operator?)
I stumbled across this idea, and thought it was fantastic.
Take a look at this a minute. We can do it all simply, and in one line! This seems to be a standard SQL construct, as it works similarly across a few of them (silly MySQL uses rand() instead) including SQLite.
This has the benefit of being simple, and would seem to be faster as it is happening in the database.
Unfortunately, its not that clear. Each database does this differently (generally adds a column to each row in your table, in memory), and it doesn’t scale well (remember that :limit doesn’t can’t get applied until the result set is fully built).
After I put together a plugin and a patch to make this transparent, I came across a Rails ticket that got flagged ‘wontfix’. Jeremy Kemper’s response to this idea was:
order by rand() limit 1 is very taxing on the database. It shouldn’t be a standard nor encouraged feature.
Jeremy certainly isn’t wrong, but I’m not sure he’s fully right either. We have a solution that won’t be usable with large tables (or a high-traffic site), but technically works well.
Here’s the working plugin. which lets you pass :random to any finder.
It works as with MySQL, SQLite and PostgreSQL. Do listen to Jeremy’s warning. I’m using it on a an app with a dataset that will stay relatively small. If you run into scaling issues, don’t say we didn’t warn you.
Anyone have a better solution?
Mislav — May 20, 2007
Here’s a better solution:
Thing.find :first, :offset => rand(Thing.count)It’s 2 queries, but it scales because the first query is just a count (which databases optimize quickly). You should order by some column and have an index on that column, naturally (that goes without saying).
Daniel Morrison — May 21, 2007
@Mislav: That does sound quicker, though it doesn’t help with multiple records.
Mislav — May 21, 2007
Depending on how much records do you need and how large is the table, this may be a better solution even for multiple records. Just cache the count and issue the finder multiple times. Even 5 queries can be faster than selecting 5 rows with RAND is the way. I just don’t see what might be the purpose for fetching so much random rows.
hiroshi — May 21, 2007
I just tell you that the URL you specified is wrong
- http://source.collectiveidea.com/public/rails/plugins/imap_authenticatable
+ http://source.collectiveidea.com/public/rails/plugins/random_finders
Daniel Morrison — May 22, 2007
@hiroshi: thanks, fixed.
@Mislav: I get what you’re saying now, do the RAND in Ruby, and get the rows that way (IN condition seems best). I’ll update the plugin shortly.
In reality, I can’t see why you’d want that many rows either. I think most people would want a few (5-10) max, so we can optimize to that.
sjs — June 05, 2007
I’m working on an app right now where customers buy anywhere from 1-100 (possibly more) “things”, and they should receive these randomly from a table with millions of rows. I’m pretty sure that I’ll run into scaling issues using find :all, :order => 'rand()', :limit => n so I have a feeling I’ll benchmark the 2 methods fairly soon to see which is better for us. Even with 100 queries I think Mislav’s solution sounds faster.
Daniel Morrison — June 07, 2007
@sjs: That would be great! If you get any benchmarks I’d love to link to them.
I expect Mislav’s solution to be quicker too.
ruby bonk — December 24, 2007
Great discussion here. Thanks to you and Mislav for pointing out many solutions and drawbacks/benefits.
Since mine is a large-data-set thing, i’m going w/ Mislav’s idea.
Thanks again for the post!
Dan Parker — March 21, 2008
I know SQL does a lot of math quite happily too – there should be a way to create a query that has a mathematical algorithm centered around random() that can do it quite efficiently. Thinking out loud here as I research…
For starters, you can use “FLOOR*180))” to get a random integer between 20 and 200. If you can assume that most numbers exist in your id’s, you could grab a random match: “id IN (FLOOR*highest_id_in_table)))”.
Now, you’d need to gather that highest_id_in_table. The following is a start:
SELECT * FROM logs WHERE id IN (FLOOR(@highest := (SELECT MAX FROM logs)))), FLOOR@highest)));
- but I’m not sure if it’s executing the (SELECT MAX FROM logs) every time it compares a record, or not. With just a little more work (I have to go now, the evening activity is calling :P), you could get the @highest (a client variable in sql) to set only if it hasn’t been set yet. In any case, it gets carried over the rest of the equation. In fact, if you run that query, you’ll see very random results - on a table of over 3000 records, I got anywhere from 0 to 5 records returned. That was some good learning, anyway.
My real suggestion is to run two queries:
1) top = SELECT MAX as top FROM logs; #not a big overhead on an indexed integer column
2) Gather 6 random ruby bits: r = [rand(top),rand(top),…]
3) records = [“SELECT * FROM logs WHERE id IN (?) GROUP BY id LIMIT 3”, r]
Julem — November 28, 2022
This conversation is excellent. Thanks to you and Mislav for highlighting so many fixes and advantages/disadvantages. octordle
Mariah Carey — March 02, 2023
Another approach is to use the OFFSET clause in SQL to skip a random number of records, wordle and then return the next 3 records.
Betsy Jolas — March 02, 2023
There are several ways to select random records from a database table in Rails, each with its own tradeoffs. Depending on the specific requirements of the application, weaver one approach may be more suitable than the others.
sdfgvsdf — March 15, 2023
The forum that you share is fantastic and has a lot of significance; I really hope that it will become more widespread. stumble guys