Database query optimization in Ruby on Rails - Mini Project: Build & Apply

Database query optimization

📖 Scenario: You are building a simple blog application using Ruby on Rails. The app has Post and Comment models. Each post can have many comments.Currently, the app loads all posts and then loads comments for each post separately. This causes many database queries and slows down the app.

🎯 Goal: Optimize the database queries to load posts and their comments efficiently using Rails techniques.

📋 What You'll Learn

Create a Post model with a title and body

Create a Comment model with content and a post_id

Write a query to load all posts

Optimize the query to include comments to avoid N+1 queries

💡 Why This Matters

🌍 Real World

Optimizing database queries is essential for building fast and scalable web applications that handle related data efficiently.

💼 Career

Understanding query optimization in Rails is a key skill for backend developers to improve app performance and user experience.

1

Create Post and Comment models

Create a Post model with attributes title and body, and a Comment model with attributes content and post_id. Set up the association so that Post has many comments and Comment belongs to post.

Ruby on Rails

# Define Post and Comment models with associations
# Your code here

Hint

Use has_many :comments in Post and belongs_to :post in Comment.

2

Load all posts

Write a query to load all posts from the database and assign it to a variable called posts.

Ruby on Rails

class Post < ApplicationRecord
  has_many :comments
end

class Comment < ApplicationRecord
  belongs_to :post
end

# Load all posts
# Your code here

Hint

Use Post.all to get all posts.

3

Optimize query with includes

Modify the query to load all posts and their associated comments in one query using includes(:comments). Assign the result to the variable posts.

Ruby on Rails

class Post < ApplicationRecord
  has_many :comments
end

class Comment < ApplicationRecord
  belongs_to :post
end

# Optimize query to include comments
# Your code here

Hint

Use Post.includes(:comments).all to eager load comments.

4

Use optimized posts in view

Write a loop to iterate over posts and for each post, iterate over its comments to display the comment content. Use posts.each do |post| and post.comments.each do |comment|.

Ruby on Rails

class Post < ApplicationRecord
  has_many :comments
end

class Comment < ApplicationRecord
  belongs_to :post
end

posts = Post.includes(:comments).all

# Loop through posts and comments
# Your code here

Hint

Use nested loops: posts.each do |post| and inside it post.comments.each do |comment|.

Practice

1. What is the main benefit of using includes in Rails queries?

easy

A. It loads associated records in fewer database queries to improve performance.

B. It deletes unused records from the database automatically.

C. It encrypts data before saving to the database.

D. It creates new database tables for associations.

Solution

1. Step 1: Understand what includes does

   includes loads related records in one or fewer queries to avoid repeated database hits.

2. Step 2: Identify the benefit

   This reduces the number of queries, speeding up the app and saving server resources.

3. Final Answer:

   It loads associated records in fewer database queries to improve performance. -> Option A

4. Quick Check:

   Using includes reduces queries = B [OK]

Hint: Think: fewer queries means faster loading [OK]

Common Mistakes:

• Confusing includes with deleting records
• Thinking includes encrypts data
• Assuming includes creates tables

2. Which of the following is the correct syntax to select only the name and email columns from a User model in Rails?

easy

A. User.find(:name, :email)

B. User.where(:name, :email)

C. User.includes(:name, :email)

D. User.select(:name, :email)

Solution

1. Step 1: Recall the method to choose specific columns

   The select method specifies which columns to retrieve from the database.

2. Step 2: Check the syntax

   User.select(:name, :email) correctly selects only the name and email columns.

3. Final Answer:

   User.select(:name, :email) -> Option D

4. Quick Check:

   Select columns = select method = D [OK]

Hint: Use select to pick columns, not where or includes [OK]

Common Mistakes:

• Using where instead of select for columns
• Using includes to select columns
• Using find with multiple columns

3. Given the following Rails code:

posts = Post.includes(:comments).where(published: true)

What is the main effect of this query?

medium

A. It loads all posts and their comments, regardless of published status.

B. It loads only comments for published posts but not the posts themselves.

C. It loads only published posts and their comments efficiently in fewer queries.

D. It deletes unpublished posts and their comments.

Solution

1. Step 1: Analyze the query filters and includes

   The where(published: true) filters posts to only those published. includes(:comments) loads comments with posts efficiently.

2. Step 2: Understand the combined effect

   The query fetches published posts and their comments in fewer database queries, improving performance.

3. Final Answer:

   It loads only published posts and their comments efficiently in fewer queries. -> Option C

4. Quick Check:

   Filter + includes = efficient published posts with comments = C [OK]

Hint: Filter first, then includes loads related data efficiently [OK]

Common Mistakes:

• Thinking includes ignores the where filter
• Assuming comments load without posts
• Confusing includes with deletion

4. Identify the problem in this Rails query and how to fix it:

users = User.where(active: true).includes(:posts).select(:name)

medium

A. The select excludes the primary key (:id), causing posts not to load; fix by including :id in select.

B. The includes method is used incorrectly; replace it with joins.

C. The where clause should come after select to filter correctly.

D. The query will delete inactive users; remove where clause.

Solution

1. Step 1: Understand select limits columns

   The select(:name) limits columns returned, but the primary key id is missing.

2. Step 2: Effect on includes

   Without the primary key, Rails cannot load associated posts properly with includes.

3. Step 3: Fix the query

   Add the primary key column (e.g., :id) to the select list to allow loading posts.

4. Final Answer:

   The select excludes the primary key (:id), causing posts not to load; fix by including :id in select. -> Option A

5. Quick Check:

   Select must include primary key for includes to work = A [OK]

Hint: Always select primary keys when using includes [OK]

Common Mistakes:

• Removing primary key from select breaks associations
• Replacing includes with joins unnecessarily
• Changing where order incorrectly

5. You want to optimize a query to fetch Order records with their Customer and only the total_price and created_at fields from orders. Which Rails query is best for this optimization?

hard

A. Order.joins(:customer).select('orders.total_price, orders.created_at, customers.*')

B. Order.includes(:customer).select(:total_price, :created_at, :customer_id)

C. Order.includes(:customer).select(:total_price, :created_at)

D. Order.all.select(:total_price, :created_at)

Solution

1. Step 1: Identify needed fields and associations

   You want orders with only total_price and created_at, plus associated customers.

2. Step 2: Ensure foreign key is selected for includes

   Using includes(:customer) requires selecting the foreign key customer_id to load customers efficiently.

3. Step 3: Choose the query that selects needed fields and includes association correctly

   Order.includes(:customer).select(:total_price, :created_at, :customer_id) meets all requirements.

4. Final Answer:

   Order.includes(:customer).select(:total_price, :created_at, :customer_id) -> Option B

5. Quick Check:

   Select needed fields + foreign key + includes = A [OK]

Hint: Include foreign keys in select when using includes [OK]

Common Mistakes:

• Omitting foreign key breaks includes
• Using joins loads all customer fields unnecessarily
• Selecting all orders without includes