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(' | ') }
Rails has built-in support for MySQL's EXPLAIN functionality
Reservation.joins(:bookings).where(bookings: { state: 'active' }).explain
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
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 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.
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.