Index selection strategy in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Effect of Index on Query Result

Consider a table employees with columns id, name, and department_id. An index exists on department_id. What will be the output of the following query?

SELECT department_id, COUNT(*) AS total FROM employees GROUP BY department_id;

Assume the table has 3 departments with 5, 7, and 8 employees respectively.

MySQL

SELECT department_id, COUNT(*) AS total FROM employees GROUP BY department_id;

A [{"department_id": 1, "total": 5}, {"department_id": 2, "total": 7}, {"department_id": 3, "total": 8}]

B [{"department_id": 1, "total": 7}, {"department_id": 2, "total": 5}, {"department_id": 3, "total": 8}]

C [{"department_id": 1, "total": 8}, {"department_id": 2, "total": 7}, {"department_id": 3, "total": 5}]

D [{"department_id": 1, "total": 20}]

💡 Hint

Grouping counts employees per department. The index helps speed but does not change counts.

The query groups employees by department_id and counts them. The index on department_id speeds up grouping but does not affect the counts. The counts match the given numbers per department.

🧠 Conceptual

intermediate

Choosing Index for Query Optimization

You have a table orders with columns order_id, customer_id, order_date, and status. Which index would best optimize this query?

SELECT * FROM orders WHERE customer_id = 123 AND status = 'shipped';

A An index on (customer_id, status)

B No index needed

C An index on (status, order_date)

D An index on (order_date)

💡 Hint

Think about which columns appear in the WHERE clause together.

The query filters by customer_id and status. An index on both columns in that order helps the database quickly find matching rows.

📝 Syntax

advanced

Syntax for Creating a Composite Index

Which of the following is the correct syntax to create a composite index on columns last_name and first_name in MySQL?

A CREATE INDEX idx_name ON employees (last_name) AND (first_name);

B CREATE INDEX idx_name ON employees last_name, first_name;

C CREATE INDEX idx_name (last_name, first_name) ON employees;

D CREATE INDEX idx_name ON employees (last_name, first_name);

💡 Hint

Remember the order of clauses in the CREATE INDEX statement.

The correct syntax is CREATE INDEX index_name ON table_name (column1, column2);. Option D follows this exactly.

❓ optimization

advanced

Index Selection for Range Queries

Given a table products with columns product_id, category_id, and price, which index will best optimize this query?

SELECT * FROM products WHERE category_id = 10 AND price BETWEEN 100 AND 200;

A An index on (price, category_id)

B An index on (category_id) only

C An index on (category_id, price)

D An index on (price) only

💡 Hint

Consider the order of columns in the index for equality and range conditions.

For queries with equality on the first column and range on the second, the index should have the equality column first. So (category_id, price) is best.

🔧 Debug

expert

Why Does This Index Not Improve Query Performance?

A developer created an index on email column of the users table to speed up this query:

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

Why does the index not improve performance?

A Because the index was created on the wrong column

B Because the LIKE pattern starts with a wildcard, the index cannot be used efficiently

C Because the query is missing an ORDER BY clause

D Because the email column is not unique

💡 Hint

Think about how indexes work with LIKE patterns.

Indexes can be used efficiently only if the LIKE pattern does not start with a wildcard. Starting with '%' prevents the database from using the index to speed up the search.