Index creation in Ruby on Rails

Introduction

Indexes help the database find data faster, like a shortcut in a book's table of contents.

When you want to speed up searches on a column in your database table.

When you often look up records by a specific field, like user email or product ID.

When you want to enforce uniqueness so no two rows have the same value in a column.

When you join tables on certain columns frequently and want faster results.

Syntax

Ruby on Rails

add_index :table_name, :column_name, options_hash

Use add_index inside a Rails migration file.

You can add options like unique: true to prevent duplicate values.

Examples

Adds a simple index on the email column of the users table.

Ruby on Rails

add_index :users, :email

Adds a multi-column index on category_id and price in the products table.

Ruby on Rails

add_index :products, [:category_id, :price]

Adds a unique index on username to ensure no duplicates.

Ruby on Rails

add_index :users, :username, unique: true

Sample Program

This migration adds a unique index on the email column in the users table to speed up lookups and prevent duplicate emails.

Ruby on Rails

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

Important Notes

Adding indexes speeds up queries but can slow down data inserts and updates.

Always add indexes through migrations to keep your database schema consistent.

Use unique indexes to enforce data rules like no duplicate emails.

Summary

Indexes make data searches faster by creating shortcuts.

Use add_index in Rails migrations to create indexes.

Unique indexes prevent duplicate values in a column.

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