Index creation in Ruby on Rails - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is an index in a database?

An index is like a shortcut that helps the database find data faster, similar to an index in a book that points to pages quickly.

beginner

How do you create an index on a column in Rails migrations?

You use add_index :table_name, :column_name inside a migration file to create an index on that column.

intermediate

Why should you use indexes carefully?

Indexes speed up reading data but slow down writing data because the index needs updating. So, use them only on columns you search often.

intermediate

What does a unique index do?

A unique index makes sure that all values in the indexed column are different, preventing duplicate entries.

beginner

How do you remove an index in Rails?

You use remove_index :table_name, :column_name in a migration to delete an index.

Which Rails migration command creates an index on the 'email' column of the 'users' table?

A add_column :users, :email_index

B create_index :users, :email

C add_index :users, :email

D index_add :users, :email

The correct syntax to add an index is add_index :table_name, :column_name.

What is the main benefit of adding an index to a database column?

A Faster data retrieval

B More storage space

C Slower queries

D Automatic backups

Indexes help the database find data faster, improving query speed.

What is a potential downside of having too many indexes?

A Slower data insertion and updates

B Faster data insertion

C No effect on performance

D Database crashes

Indexes need updating when data changes, which can slow down insert and update operations.

How do you enforce uniqueness of values in a column using an index in Rails?

A add_unique_index :table, :column

B add_index :table, :column, unique: true

C add_index :table, :column, unique: false

D create_unique :table, :column

You add unique: true option to add_index to enforce uniqueness.

Which command removes an index on the 'username' column in the 'accounts' table?

A drop_index :accounts, :username

B remove_column :accounts, :username

C delete_index :accounts, :username

D remove_index :accounts, :username

The correct command to remove an index is remove_index :table_name, :column_name.

Explain what a database index is and why it is useful.

Think about how you find a topic quickly in a book.

Describe how to create and remove an index in Rails migrations.

Use Rails migration commands for managing indexes.

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