Index creation in Ruby on Rails - Step-by-Step Execution

Concept Flow - Index creation

Start: Define index creation

↓

Write migration file

↓

Run migration

↓

Database creates index

↓

Query uses index for faster search

↓

End

This flow shows how you create an index in Rails: write a migration, run it, and then the database uses the index to speed up queries.

Execution Sample

Ruby on Rails

class AddIndexToUsersEmail < ActiveRecord::Migration[7.0]
  def change
    add_index :users, :email
  end
end

This migration adds an index on the email column of the users table to speed up email lookups.

Execution Table

Step	Action	Details	Result
1	Write migration	Define add_index on users.email	Migration file created
2	Run migration	rails db:migrate executes migration	Index created in database
3	Database updates	Index structure built on users.email	Index ready for use
4	Query execution	Search users by email	Query uses index, faster search
5	End	No more steps	Process complete

💡 Index created and ready, queries on users.email will be faster

Variable Tracker

Variable	Start	After Migration Run	After Index Creation	Final
migration_file	not created	created	created	created
database_index	none	exists on users.email	exists on users.email	exists on users.email
query_speed	normal	normal	faster	faster

Key Moments - 3 Insights

Why do we need to run the migration after writing it?

Writing the migration only creates the file. Running it applies the change to the database, actually creating the index (see execution_table step 2).

Does adding an index change the data in the table?

No, the data stays the same. The index is a separate structure that helps find data faster (see execution_table step 3).

How does the index make queries faster?

The database uses the index to quickly locate rows without scanning the whole table (see execution_table step 4).

Visual Quiz - 3 Questions

Look at the execution_table, at which step is the index actually created in the database?

A Step 2

B Step 1

C Step 4

D Step 3

💡 Hint

Check the 'Result' column in execution_table rows for when the index is created.

According to variable_tracker, what is the state of query_speed after the index creation?

A normal

B faster

C slower

D unchanged

💡 Hint

Look at the 'query_speed' row in variable_tracker after 'After Index Creation'.

If you skip running the migration, what will be the state of database_index?

A partially created

B exists on users.email

C none

D corrupted

💡 Hint

Refer to variable_tracker 'database_index' before and after migration run.

Concept Snapshot

Index creation in Rails:
- Write a migration with add_index :table, :column
- Run rails db:migrate to apply
- Database builds index structure
- Queries on that column become faster
- Data remains unchanged, only lookup speed improves

Full Transcript

In Rails, creating an index involves writing a migration file that uses add_index to specify the table and column. After writing, you run the migration with rails db:migrate, which tells the database to build the index. This index is a special structure that helps the database find rows faster when you search by that column. The data in the table does not change, only the speed of queries improves. The process ends with the index ready and queries running faster.

Practice

1. What is the main purpose of creating an index in a Rails database?

easy

A. To store more data in the database

B. To speed up data searches by creating shortcuts

C. To delete duplicate records automatically

D. To backup the database regularly

Solution

1. Step 1: Understand what an index does

   An index helps the database find data faster by creating a shortcut to the data location.

2. Step 2: Compare options with the purpose

   Only To speed up data searches by creating shortcuts correctly describes speeding up data searches, while others describe unrelated tasks.

3. Final Answer:

   To speed up data searches by creating shortcuts -> Option B

4. Quick Check:

   Index = Faster search [OK]

Hint: Indexes speed up searches by creating shortcuts [OK]

Common Mistakes:

• Thinking indexes store more data
• Confusing indexes with backups
• Believing indexes delete duplicates automatically

2. Which of the following is the correct syntax to add an index on the email column of the users table in a Rails migration?

easy

A. add_index :users, :email

B. add_index users, email

C. addIndex(:users, :email)

D. index_add :users, :email

Solution

1. Step 1: Recall Rails migration syntax for adding index

   The correct method is add_index with the table name as a symbol and the column name as a symbol.

2. Step 2: Check each option's syntax

   add_index :users, :email uses correct symbols and method name. The other options have syntax errors or wrong method names like missing symbols, camelCase, or incorrect method.

3. Final Answer:

   add_index :users, :email -> Option A

4. Quick Check:

   Correct Rails syntax = add_index :table, :column [OK]

Hint: Use symbols and method name exactly: add_index :table, :column [OK]

Common Mistakes:

• Omitting colons for symbols
• Using wrong method names like addIndex
• Passing table and column without symbols

3. Given this migration code:

add_index :orders, :user_id, unique: true

What will be the effect of this index?

medium

A. It allows multiple orders with the same user_id

B. It creates a non-unique index on user_id

C. It deletes orders with duplicate user_id automatically

D. It prevents duplicate user_id values in orders

Solution

1. Step 1: Understand the unique option in add_index

   Adding unique: true creates a unique index that prevents duplicate values in the indexed column.

2. Step 2: Analyze the effect on user_id in orders

   This means no two orders can have the same user_id, so duplicates are prevented.

3. Final Answer:

   It prevents duplicate user_id values in orders -> Option D

4. Quick Check:

   unique: true = no duplicates allowed [OK]

Hint: unique: true means no duplicate values allowed [OK]

Common Mistakes:

• Thinking unique allows duplicates
• Confusing unique with non-unique index
• Believing it deletes duplicates automatically

4. You wrote this migration code:

add_index :products, :name, unique: true, name: 'index_products_on_name_which_is_too_long_for_postgresql_63_char_limit'

But running migration gives an error. What is the likely problem?

medium

A. The index name is too long

B. The name option is not allowed in add_index

C. The syntax is missing parentheses

D. The name option should be index_name

Solution

1. Step 1: Understand index name length limits

   Database systems like PostgreSQL limit index name length (usually 63 characters). A too long name causes errors.

2. Step 2: Check the given index name

   The name 'index_products_on_name_which_is_too_long_for_postgresql_63_char_limit' exceeds the database's maximum identifier length (typically 63 characters in PostgreSQL), causing the migration to fail.

3. Step 3: Confirm other options

   The name option is valid in add_index, parentheses are optional in Ruby, and index_name is not a valid option.

4. Final Answer:

   The index name is too long -> Option A

5. Quick Check:

   Index name length limit causes errors [OK]

Hint: Check index name length if migration errors occur [OK]

Common Mistakes:

• Thinking name option is invalid
• Adding unnecessary parentheses
• Using wrong option names like index_name

5. You want to create a composite unique index on first_name and last_name columns in the employees table to prevent duplicate full names. Which migration code is correct?

hard

A. add_index :employees, first_name, last_name, unique: true

B. add_index :employees, :first_name, :last_name, unique: true

C. add_index :employees, [:first_name, :last_name], unique: true

D. add_index :employees, [:first_name, :last_name], unique: false

Solution

1. Step 1: Understand composite index syntax

   To create an index on multiple columns, pass an array of symbols for those columns.

2. Step 2: Check uniqueness requirement

   Setting unique: true ensures no duplicate combinations of first_name and last_name.

3. Step 3: Evaluate options

   add_index :employees, [:first_name, :last_name], unique: true correctly uses an array and unique true. The other options have wrong argument formats (separate arguments instead of array, missing symbols) or set unique: false, which does not prevent duplicates.

4. Final Answer:

   add_index :employees, [:first_name, :last_name], unique: true -> Option C

5. Quick Check:

   Composite unique index = array + unique: true [OK]

Hint: Use array for multiple columns and unique: true for uniqueness [OK]

Common Mistakes:

• Passing columns as separate arguments instead of array
• Forgetting unique: true for uniqueness
• Using unique: false when uniqueness is needed