Debugging Slow Queries in Rails

slow_queries_in_rails.md

🐌 On slow DB queries in Rails

🔍 Show slow process

You can use Rails Console to if queries are hanging.

connection = ActiveRecord::Base.connection
puts connection.execute("SHOW PROCESSLIST").map { |e| e.join(' | ') }
puts connection.execute("SHOW FULL PROCESSLIST").map { |e| e.join(' | ') }

🤔 Explain a slow query

Rails has built-in support for MySQL's EXPLAIN functionality

Reservation.joins(:bookings).where(bookings: { state: 'active' }).explain

🐘 Things to remember

🍎 When to use .includes

Use .includes to avoid N+1 when we need to access those associations in views

# Controller
@reservations = Reservation.includes(:bookings).all

# View
@reservations.each do |reservation|
  puts reservation.bookings.size
end

🍊 When to use .joins

Use .joins if you're filtering by associations using .where, but don't need those associations in memory

@reservations = Reservation.joins(:bookings).where(bookings: { state: 'active' })

note that the above (.joins) uses an INNER JOIN (you can append any query with .to_sql) whereas the below (.includes) uses LEFT OUTER JOIN

@reservations = Reservation.includes(:bookings).where(bookings: { state: 'active' })

🍎 🍊 When to (maybe) use both

When very large datasets are being queried or multiple includes are being used, we've seen significant performance degradation when a .where statement follows a heavy .includes.

If you need to filter and preload, then it's perfectly okay to use .joins + .where + .includes (in that order)

e.g.

@reservations = Reservation.joins(:bookings).where(bookings: { state: 'active' }).includes(:bookings)

This isn't always faster. But if you're debugging a slow query, we've seen huge benefits doing it this way.

🍌 Other curiosites

We've also seen significant performance improvements when passing ActiveRecord::Relation objects into scopes, rather than an Array.

# scope usage
Price.for_bookable(bookables)

# ActiveRecord::Relation (fast)
bookables = Bookable.where(id: bookable_ids)

# Array (slow)
bookables = Bookable.find(bookable_ids)

It stands to some reason that Active Record will find a faster way to handle an ActiveRecord::Relation in a query. But, again, we have been surprised at significance of the speed increase.