Database query optimization in Ruby on Rails - Step-by-Step Execution

Concept Flow - Database query optimization

Write ActiveRecord query

↓

Rails builds SQL query

↓

Send SQL to DB

↓

DB executes query

↓

Return results

↓

Render or use data

↓

If slow, analyze query

↓

Add indexes or optimize query

↩Back to Write ActiveRecord query

This flow shows how Rails turns your code into SQL, runs it, and how you optimize by analyzing and improving queries.

Execution Sample

Ruby on Rails

users = User.where(active: true).includes(:posts).limit(5)
users.each do |user|
  puts user.name
  puts user.posts.count
end

This code fetches 5 active users and their posts efficiently, then prints user names and post counts.

Execution Table

Step	Action	Rails Query	SQL Query	DB Result	Notes
1	Build ActiveRecord query	User.where(active: true).includes(:posts).limit(5)	SELECT "users".* FROM "users" WHERE "users"."active" = TRUE LIMIT 5	N/A	Rails prepares SQL with conditions and limit
2	Send SQL to DB	N/A	SELECT "users".* FROM "users" WHERE "users"."active" = TRUE LIMIT 5	5 user rows	DB returns 5 active users
3	Build includes query	N/A	SELECT "posts".* FROM "posts" WHERE "posts"."user_id" IN (1, 2, 3, 4, 5)	Posts for those users	Rails fetches posts in one query to avoid N+1
4	Iterate users	N/A	N/A	N/A	Loop over users to print name and post count
5	Print user.name	N/A	N/A	User name string	Output user name
6	Print user.posts.count	N/A	N/A	Number of posts	Output post count without extra queries
7	End loop	N/A	N/A	N/A	All users processed
8	If slow, analyze	N/A	EXPLAIN SELECT * FROM users	Query plan	Check for missing indexes or inefficient queries
9	Add index on users.active	N/A	CREATE INDEX index_users_on_active ON users(active)	N/A	Speed up WHERE active = true
10	Re-run query	User.where(active: true).includes(:posts).limit(5)	Optimized SQL	Faster results	Improved performance

💡 Loop ends after processing 5 users; optimization done if queries are slow

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 6	Final
users	nil	Array of 5 User objects	Array of 5 User objects with posts loaded	Same array	Same array
user	nil	First user object	First user object	First user object	Last user object after loop
user.posts	nil	N/A	Array of posts for user	Same array	Same array
user.posts.count	nil	N/A	N/A	Number of posts	N/A

Key Moments - 3 Insights

Why does using includes(:posts) prevent extra queries inside the loop?

Because includes loads all posts for the users in one query (Step 3), so inside the loop (Step 6) it doesn't query the database again.

What happens if we don't add an index on users.active?

The database scans all users to find active ones, making Step 2 slower. Adding the index (Step 9) speeds this up.

Why do we limit the query to 5 users?

To avoid loading too many records at once, which keeps the query fast and memory use low (Step 1 and Step 7).

Visual Quiz - 3 Questions

Look at the execution table, what SQL query does Rails send first to the database?

A SELECT "users".* FROM "users" WHERE "users"."active" = TRUE LIMIT 5

B SELECT "posts".* FROM "posts" WHERE "posts"."user_id" IN (user_ids)

C CREATE INDEX index_users_on_active ON users(active)

D EXPLAIN SELECT * FROM users

💡 Hint

Check Step 2 in the execution_table for the first SQL query sent

At which step does Rails fetch all posts for the selected users to avoid extra queries?

A Step 2

B Step 3

C Step 5

D Step 9

💡 Hint

Look at the execution_table row describing fetching posts in one query

If we remove the limit(5) from the query, what changes in the execution_table?

A Step 9 will create a different index

B Step 3 will not run

C Step 2 will return more than 5 users

D Step 6 will print fewer user names

💡 Hint

Refer to Step 2 and the limit effect on number of users returned

Concept Snapshot

Database query optimization in Rails:
- Use ActiveRecord methods like where, includes, limit
- includes(:association) loads related data in one query to avoid N+1 problem
- Add database indexes on columns used in WHERE for faster lookups
- Use EXPLAIN to analyze query performance
- Limit records fetched to improve speed and memory
- Optimize by iterating and improving queries step-by-step

Full Transcript

This visual execution shows how Rails converts an ActiveRecord query into SQL, sends it to the database, and fetches results. It demonstrates how includes loads associated records in one query to avoid extra database calls inside loops. The example limits results to 5 users for performance. It also shows how adding an index on the active column speeds up queries. The variable tracker follows the users and posts data as it loads and prints. Key moments clarify why includes prevents extra queries, why indexes matter, and why limiting results helps. The quiz tests understanding of query steps and optimization effects.

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