34. Query Optimization

1# N+1 Problem - BAD
2users = User.all
3users.each do |user|
4  puts user.articles.count  # One query per user!
5end
6# 1 query for users + N queries for articles = N+1 queries
7
8# Solution - GOOD
9users = User.includes(:articles)
10users.each do |user|
11  puts user.articles.size  # No additional queries
12end
13# Only 2 queries total

1# Gemfile
2group :development do
3  gem 'bullet'
4end
5
6# config/environments/development.rb
7config.after_initialize do
8  Bullet.enable = true
9  Bullet.alert = true
10  Bullet.bullet_logger = true
11  Bullet.console = true
12  Bullet.rails_logger = true
13end

1# Enable query logging
2ActiveRecord::Base.logger = Logger.new(STDOUT)
3
4# Check logs for repeated queries

1# Uses separate queries or JOIN depending on conditions
2User.includes(:articles)
3# Query 1: SELECT * FROM users
4# Query 2: SELECT * FROM articles WHERE user_id IN (1, 2, 3...)
5
6# With conditions (uses JOIN)
7User.includes(:articles).where(articles: { published: true }).references(:articles)

1# Always uses separate queries
2User.preload(:articles)
3# Query 1: SELECT * FROM users
4# Query 2: SELECT * FROM articles WHERE user_id IN (1, 2, 3...)
5
6# Cannot filter on preloaded association
7User.preload(:articles).where(articles: { published: true })  # Error!

1# Always uses LEFT OUTER JOIN
2User.eager_load(:articles)
3# SELECT users.*, articles.* FROM users 
4# LEFT OUTER JOIN articles ON articles.user_id = users.id
5
6# Good when you need to filter
7User.eager_load(:articles).where(articles: { published: true })

1# includes - Default choice, Rails picks best strategy
2User.includes(:articles, :comments)
3
4# preload - When you don't filter on associations
5User.preload(:articles)
6
7# eager_load - When filtering on associations
8User.eager_load(:articles).where(articles: { published: true })
9
10# Nested eager loading
11User.includes(articles: [:comments, :tags])

1# Without counter cache - hits database each time
2user.articles.count  # SELECT COUNT(*) FROM articles WHERE user_id = 1
3
4# Add counter cache
5class Article < ApplicationRecord
6  belongs_to :user, counter_cache: true
7end
8
9# Migration
10add_column :users, :articles_count, :integer, default: 0, null: false
11
12# Backfill existing data
13User.find_each do |user|
14  User.reset_counters(user.id, :articles)
15end
16
17# Now this is instant
18user.articles_count  # No query!
19user.articles.size   # Uses counter cache automatically

1# Migration
2class AddIndexesToUsers < ActiveRecord::Migration[7.1]
3  def change
4    # Single column index
5    add_index :users, :email
6    
7    # Unique index
8    add_index :users, :email, unique: true
9    
10    # Composite index
11    add_index :articles, [:user_id, :created_at]
12    
13    # Partial index (PostgreSQL)
14    add_index :users, :email, where: "active = true"
15    
16    # Index for foreign keys
17    add_index :articles, :user_id
18    add_index :articles, :category_id
19  end
20end

1# Gemfile
2gem 'lol_dba'
3
4# Run
5rails db:find_indexes

1# Bad - loads all columns
2users = User.all
3users.map(&:email)
4
5# Good - loads only needed columns
6emails = User.pluck(:email)
7
8# Or if you need objects
9users = User.select(:id, :email)
10users.map(&:email)

1# Bad - loads all records into memory
2User.all.each do |user|
3  user.send_newsletter
4end
5
6# Good - loads in batches
7User.find_each(batch_size: 1000) do |user|
8  user.send_newsletter
9end
10
11# Or get arrays of batches
12User.find_in_batches(batch_size: 1000) do |users|
13  bulk_send_newsletter(users)
14end

1# Bad - loads unnecessary columns
2Article.where(published: true).each do |article|
3  puts article.title
4end
5
6# Good - load only what you need
7Article.where(published: true).pluck(:title).each do |title|
8  puts title
9end
10
11# Or with select
12Article.select(:id, :title, :slug).where(published: true)

1# Slow - counts all matching records
2if User.where(role: "admin").count > 0
3  # ...
4end
5
6# Fast - stops at first match
7if User.where(role: "admin").exists?
8  # ...
9end
10
11# Also faster
12if User.where(role: "admin").any?
13  # ...
14end

1class User < ApplicationRecord
2  def self.admin_count
3    Rails.cache.fetch("admin_count", expires_in: 1.hour) do
4      where(role: "admin").count
5    end
6  end
7end
8
9# Fragment caching in views
10<% cache @article do %>
11  <%= @article.title %>
12  <%= @article.body %>
13<% end %>

1# See how database executes query
2Article.where(published: true).explain
3
4# Output example:
5# EXPLAIN for: SELECT * FROM articles WHERE published = true
6# Seq Scan on articles  (cost=0.00..1.05 rows=5 width=72)
7#   Filter: (published = true)

1# Use where instead of select in Ruby
2# Bad
3users.select { |u| u.active? }
4
5# Good
6User.where(active: true)
7
8# Use update_all for bulk updates
9# Bad
10User.where(role: "guest").each { |u| u.update(role: "user") }
11
12# Good
13User.where(role: "guest").update_all(role: "user")
14
15# Use delete_all when you don't need callbacks
16# Slower (runs callbacks)
17User.where(deleted: true).destroy_all
18
19# Faster (skips callbacks)
20User.where(deleted: true).delete_all