Query optimization techniques in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Index Usage in Query Optimization

Which of the following statements best explains how indexes improve query performance in MySQL?

A Indexes create a quick lookup structure that helps MySQL find rows without scanning the whole table.

B Indexes store a sorted copy of the entire table data, allowing faster full table scans.

C Indexes duplicate all columns of a table to speed up data retrieval.

D Indexes compress the data to reduce disk space, which indirectly speeds up queries.

💡 Hint

Think about how you find a word quickly in a dictionary without reading every page.

Indexes act like a quick lookup guide, allowing MySQL to find rows matching conditions without scanning every row.

❓ query_result

intermediate

Effect of WHERE Clause on Query Execution

Given the table employees(id, name, department, salary) with an index on department, what will be the output count of this query?

SELECT COUNT(*) FROM employees WHERE department = 'Sales';

Assume there are 1000 rows total, and 200 belong to 'Sales'.

MySQL

SELECT COUNT(*) FROM employees WHERE department = 'Sales';

A 1000

B 0

C Syntax error

D 200

💡 Hint

The WHERE clause filters rows matching the condition.

The query counts only rows where department is 'Sales', which is 200 rows.

📝 Syntax

advanced

Identifying the Correct Query to Use EXPLAIN

Which of the following queries correctly uses EXPLAIN to analyze the query plan for selecting all columns from orders where status is 'pending'?

A SELECT EXPLAIN * FROM orders WHERE status = 'pending';

B EXPLAIN SELECT * FROM orders WHERE status = 'pending';

C EXPLAIN WHERE status = 'pending' SELECT * FROM orders;

D SELECT * FROM orders EXPLAIN WHERE status = 'pending';

💡 Hint

EXPLAIN is placed before the SELECT statement.

EXPLAIN must come before the SELECT statement to show the query plan.

❓ optimization

advanced

Choosing the Best Query for Performance

Which query is generally faster when retrieving the top 5 highest salaries from employees table with an index on salary?

A SELECT * FROM employees ORDER BY salary ASC LIMIT 5;

B SELECT * FROM employees WHERE salary IN (SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 5);

C SELECT * FROM employees ORDER BY salary DESC LIMIT 5;

D SELECT * FROM employees WHERE salary >= ALL (SELECT salary FROM employees);

💡 Hint

Think about how indexes help with sorting and limiting results.

Ordering by salary descending with LIMIT 5 uses the index efficiently to get top salaries quickly.

🔧 Debug

expert

Diagnosing a Slow Query with JOINs

You have two tables: customers(id, name) and orders(id, customer_id, amount). The query below runs very slowly:

SELECT customers.name, orders.amount FROM customers JOIN orders ON customers.id = orders.customer_id WHERE orders.amount > 1000;

What is the most likely cause of the slow performance?

A Missing index on <code>orders.customer_id</code> causing full table scan on orders.

B Using JOIN instead of UNION causes slowness.

C The WHERE clause should be on customers table, not orders.

D Selecting columns instead of using SELECT * causes slow query.

💡 Hint

Indexes on join columns help speed up matching rows.

Without an index on orders.customer_id, MySQL must scan the entire orders table for each customer, slowing the query.