When indexes help and when they hurt in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Effect of Index on SELECT Query Performance

Consider a table employees with 1 million rows and an index on the department_id column. Which query will likely run faster due to the index?

SQL

SELECT * FROM employees WHERE department_id = 5;

A The query will run slower because the index causes a full table scan.

B The query will fail because indexes cannot be used in WHERE clauses.

C The query performance will be the same with or without the index.

D The query will run faster because the index helps find rows with department_id = 5 quickly.

💡 Hint

Think about how indexes help find specific values quickly without scanning the whole table.

Indexes speed up queries that filter on the indexed column by allowing the database to quickly locate matching rows instead of scanning all rows.

❓ query_result

intermediate

When Indexes Hurt INSERT Performance

Given a table orders with multiple indexes, what happens to the speed of an INSERT operation?

SQL

INSERT INTO orders (order_id, customer_id, order_date) VALUES (101, 2001, '2024-01-01');

A INSERT operations are unaffected by indexes.

B INSERT operations become faster because indexes speed up data insertion.

C INSERT operations become slower because each index must be updated.

D INSERT operations fail if indexes exist.

💡 Hint

Consider what happens behind the scenes when data is added to a table with indexes.

Each index must be updated to include the new row, which adds overhead and slows down INSERT operations.

🧠 Conceptual

advanced

Choosing When to Use Indexes

Which scenario is the best candidate for adding an index?

A A column frequently used in WHERE clauses for filtering large tables.

B A column rarely used in queries and mostly NULL values.

C A column that is updated very frequently but rarely queried.

D A small table with only a few rows.

💡 Hint

Think about when indexes provide the most benefit versus when they add unnecessary overhead.

Indexes are most useful on columns frequently used to filter large datasets. Columns rarely queried or frequently updated may not benefit from indexes.

📝 Syntax

advanced

Syntax to Create an Index

Which SQL statement correctly creates an index on the email column of the users table?

A CREATE INDEX idx_email ON users(email);

B CREATE users INDEX idx_email(email);

C INDEX CREATE idx_email ON users(email);

D CREATE INDEX users.idx_email(email);

💡 Hint

Remember the standard SQL syntax for creating indexes.

The correct syntax is CREATE INDEX index_name ON table_name(column_name);

❓ optimization

expert

Impact of Too Many Indexes on Query Performance

A table has 10 indexes on various columns. What is a likely consequence when running an UPDATE statement that modifies multiple columns?

A The UPDATE performance is unaffected by the number of indexes.

B The UPDATE will be slower because all affected indexes must be updated.

C The UPDATE will fail if more than 5 indexes exist.

D The UPDATE will be faster because indexes speed up data modification.

💡 Hint

Consider how indexes affect data modification operations like UPDATE.

Each index that covers updated columns must be maintained, which adds overhead and slows down UPDATE operations.