Why indexes speed up queries in MySQL - Challenge Your Understanding

Challenge - 5 Problems

🧠 Conceptual

intermediate

How do indexes improve query speed?

Imagine you have a huge phone book and you want to find a person's phone number. Which method is faster?

1. Looking at every name one by one.
2. Using the alphabetical index to jump directly to the name.

How does this relate to database indexes?

A Indexes copy the entire database into memory for faster access.

B Indexes store all data in a separate table, so queries run on smaller tables.

C Indexes let the database jump directly to the data instead of checking every row.

D Indexes delete unnecessary data to make queries faster.

💡 Hint

Think about how you find a word in a dictionary quickly.

Indexes work like a book's index. They help the database find rows quickly by pointing to where the data is stored, avoiding a full scan.

❓ query_result

intermediate

Query speed with and without index

Given a table users(id INT, name VARCHAR(100)) with 1 million rows, which query will run faster?

SELECT * FROM users WHERE name = 'Alice';

Assuming there is an index on name, which option is true about the query speed?

A The query with the index on <code>name</code> will be much faster than without it.

B The query speed is the same with or without the index.

C The query without the index will be faster because indexes slow down reads.

D The query will fail if there is an index on <code>name</code>.

💡 Hint

Indexes help find matching rows quickly.

With an index on name, the database can quickly locate rows where name = 'Alice' without scanning all rows.

📝 Syntax

advanced

Identify the correct syntax to create an index

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

A CREATE INDEX idx_email ON customers(email);

B CREATE customers INDEX ON email;

C CREATE INDEX customers_email email;

D INDEX CREATE email ON customers;

💡 Hint

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

The correct syntax is CREATE INDEX index_name ON table_name(column_name); to create an index.

❓ optimization

advanced

Choosing the best index for a query

You have a table orders(order_id, customer_id, order_date). You often run this query:

SELECT * FROM orders WHERE customer_id = 123 AND order_date = '2024-01-01';

Which index will speed up this query the most?

A An index only on customer_id

B A composite index on (customer_id, order_date)

C An index only on order_date

D No index is needed for this query

💡 Hint

Think about which columns appear together in the WHERE clause.

A composite index on both customer_id and order_date lets the database filter rows using both conditions efficiently.

🔧 Debug

expert

Why does this query not use the index?

Given a table products(id, name, price) with an index on price, why does this query not use the index?

SELECT * FROM products WHERE price + 10 > 100;

A Because the index only works with equality conditions, not inequalities.

B Because the index on price is corrupted and unusable.

C Because the query syntax is invalid and causes an error.

D Because the condition modifies the indexed column with an expression, the index cannot be used.

💡 Hint

Indexes work best when the column is used directly in conditions.

Using an expression like price + 10 prevents the database from using the index on price because it cannot quickly look up values after modification.