How to Write Efficient Active Record Queries

Question

I am relatively new to ActiveRecord, which I use alongside Sinatra. Currently, I am building small scale websites. I have not run into any issues with queries taking too long, but I want to know how to optimize searches best, so my site is scalable.

This is something I have been wondering for awhile. Does ActiveRecord treat a query that is broken down into multiple functions different from one that is just one function?

# Single
BlogPost.last(5)

# Broken Down
BlogPost.all.limit(5)

This is more of a conceptual question: how does ActiveRecord run searches? Do more functions added make searches less efficient? When you have a query that is broken down into multiple functions BlogPost.where(publish: true) - let's say the where(publish: true) returns fifty results and the limit limits it to five. Does it first find all fifty publish: true and then cut off everything after five, or is it smart enough to stop the search after it finds the first five?

Answer 1

Your hunches are correct. It's a good practice to chain ActiveRecord queries.

Say your BlogPost table has a million rows.

To then say BlogPost.all would be slow, but to say BlogPost.all.limit(1) would be fast.

all and limit are both part of ActiveRecord. The return values respond to Array methods but are not arrays. There are some exceptions to this. find_by / find return model instances, for example, but you can put them as the last query method in a chain. pluck return a plain ruby array.

BlogPost.all[1] would be just as slow as BlogPost.all, because [ ] is an array method, not ActiveRecord.

When you chain together ActiveRecord methods, the query or queries are not executed until you convert the method to a array or attempt to read some of the data.

So query = BlogPost.all.order(id: :desc).reorder(id: :asc).limit(1) has not actually loaded any data from the database. Also read into preloading data)which can address slow N+1 queries.

If you then said query.to_a, query.reduce, etc., then Arel (what ActiveRecord is built on), finalizes the query and executes it.

You can call <query>.to_sql to see the underlying query:

class Foo < ActiveRecord::Base
end

Foo.all.to_sql
#=> "SELECT \"foos\".* FROM \"foos\""
Foo.all.limit(1).to_sql
#=> "SELECT  \"foos\".* FROM \"foos\" LIMIT 1"