Why indexes matter in SQL - Challenge Your Understanding

Challenge - 5 Problems

❓ query_result

intermediate

Effect of Index on Query Speed

Consider a table Employees with 1 million rows. It has a column LastName without an index. You run this query:

SELECT * FROM Employees WHERE LastName = 'Smith';

Now, an index is added on LastName. What is the most likely effect on the query execution?

A The query will run faster because the index allows quick lookup of 'Smith' rows.

B The query result will change and return fewer rows.

C The query will run slower because the index adds overhead to the search.

D The query will fail because indexes prevent filtering on columns.

💡 Hint

Think about how indexes help find data quickly like a book's index helps find pages fast.

Indexes speed up searches by allowing the database to jump directly to matching rows instead of scanning all rows.

🧠 Conceptual

intermediate

Why Indexes Can Slow Down Inserts

Why can adding indexes to a table slow down inserting new rows?

A Because indexes cause the database to scan all rows before inserting.

B Because the database must update the index data structure for each new row inserted.

C Because indexes lock the entire table during inserts.

D Because indexes prevent new rows from being added.

💡 Hint

Think about what happens behind the scenes when you add a new row and the index exists.

When inserting, the database updates all indexes on the table to keep them accurate, which adds extra work.

📝 Syntax

advanced

Creating an Index Syntax

Which SQL statement correctly creates an index named idx_lastname on the LastName column of the Employees table?

A INDEX CREATE idx_lastname ON Employees LastName;

B CREATE Employees INDEX idx_lastname ON LastName;

C CREATE INDEX idx_lastname ON Employees (LastName);

D CREATE INDEX ON Employees (LastName) idx_lastname;

💡 Hint

Remember the order: CREATE INDEX <name> ON <table> (<column>);

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

❓ query_result

advanced

Index Usage in Query Plans

You have a table Orders with an index on OrderDate. You run this query:

EXPLAIN SELECT * FROM Orders WHERE OrderDate = '2023-01-01';

What is the expected behavior in the query plan?

A The plan will show an index scan on OrderDate, using the index to find matching rows.

B The plan will show a full table scan ignoring the index.

C The plan will show a join operation instead of a scan.

D The plan will show an error because EXPLAIN cannot be used with indexes.

💡 Hint

EXPLAIN shows how the database plans to execute the query, including index usage.

When an index exists on the filtered column, the query plan usually shows an index scan to speed up the search.

🔧 Debug

expert

Diagnosing Slow Query Despite Index

You created an index on the Email column of the Users table. But this query is still slow:

SELECT * FROM Users WHERE Email LIKE '[e-mail]';

Why is the index not helping?

A Because indexes only work with numeric columns.

B Because the index was created on the wrong column.

C Because the query syntax is invalid and causes a full scan.

D Because the LIKE pattern starts with a wildcard '%', the index cannot be used efficiently.

💡 Hint

Think about how indexes work with string patterns and wildcards.

Indexes cannot be used efficiently when the search pattern starts with a wildcard, as the database cannot jump to a starting point.