Index creation in Ruby on Rails - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Effect of adding an index on query speed

Consider a Rails application with a users table having 1 million rows. You add an index on the email column. What is the expected effect on the following query?

SELECT * FROM users WHERE email = '[e-mail]';

A The query result will change and return fewer rows.

B The query will run slower because indexes add overhead to SELECT queries.

C The query will fail because indexes require a special syntax in SELECT statements.

D The query will run faster because the database can quickly locate the email using the index.

💡 Hint

Think about how indexes help databases find data.

Indexes allow the database to quickly find rows matching the condition without scanning the entire table, speeding up SELECT queries.

📝 Syntax

intermediate

Correct syntax to add an index in Rails migration

Which of the following is the correct way to add an index on the username column of the users table in a Rails migration?

A add_index users, username

B add_index :users, :username

C add_index(:users, username)

D add_index(:users, :username, unique: true)

💡 Hint

Remember to use symbols for table and column names.

In Rails migrations, add_index :users, :username is the standard syntax. Parentheses are optional and unique: true adds a unique constraint if needed.

❓ optimization

advanced

Choosing the best index type for a search query

You have a products table with columns name (string) and description (text). You want to optimize search queries that look for keywords in the description. Which index type is best to improve full-text search performance?

A A full-text index on the <code>description</code> column.

B A unique index on the <code>description</code> column.

C No index is needed for text search queries.

D A regular B-tree index on the <code>description</code> column.

💡 Hint

Think about which index type supports searching words inside large text fields.

Full-text indexes are designed to speed up searches for words or phrases inside large text columns, unlike regular B-tree indexes which are better for exact matches.

🔧 Debug

advanced

Why does adding an index fail in Rails migration?

You run this migration code:

add_index :orders, :customer_id, unique: true

But it fails with an error saying the index already exists. What is the most likely cause?

A An index on <code>customer_id</code> already exists, so adding another unique index causes a conflict.

B The <code>customer_id</code> column does not exist in the <code>orders</code> table.

C The syntax is incorrect; <code>unique: true</code> should be inside curly braces.

D Rails migrations do not support adding unique indexes.

💡 Hint

Check if the index already exists before adding it.

If an index on the same column already exists, trying to add another index with the same name or on the same column will cause an error.

🧠 Conceptual

expert

Impact of composite indexes on query performance

You create a composite index on (category_id, price) columns in the products table. Which of the following queries will benefit from this index?

1. SELECT * FROM products WHERE category_id = 5;
2. SELECT * FROM products WHERE price = 100;
3. SELECT * FROM products WHERE category_id = 5 AND price = 100;
4. SELECT * FROM products WHERE price = 100 AND category_id = 5;

A All queries 1, 2, 3, and 4 will benefit equally from the composite index.

B Only queries 2 and 4 will benefit from the composite index.

C Only queries 1, 3, and 4 will benefit from the composite index.

D Only query 3 will benefit; queries 1, 2, and 4 will not.

💡 Hint

Think about how composite indexes work and the order of columns.

A composite index on (category_id, price) helps queries filtering by category_id alone or both category_id and price. Queries filtering only by price cannot use this index efficiently.

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