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.