EXPLAIN query analysis in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Understanding EXPLAIN output for a simple SELECT

Given the table employees with columns id (primary key), name, and department_id, what will the EXPLAIN output show for this query?

SELECT name FROM employees WHERE id = 5;

MySQL

EXPLAIN SELECT name FROM employees WHERE id = 5;

A type: const, key: PRIMARY, rows: 1

B type: ref, key: department_id, rows: 10

C type: ALL, key: NULL, rows: 1000

D type: index, key: name, rows: 500

💡 Hint

Look for the use of the primary key in the WHERE clause.

Since the query filters by the primary key 'id', MySQL uses a constant lookup (type: const) with the PRIMARY key index, scanning only one row.

❓ query_result

intermediate

Interpreting EXPLAIN for a JOIN query

Consider two tables: orders with order_id (primary key) and customer_id, and customers with customer_id (primary key) and name. What does this EXPLAIN output indicate for the query?

SELECT o.order_id, c.name FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE c.customer_id = 10;

MySQL

EXPLAIN SELECT o.order_id, c.name FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE c.customer_id = 10;

A orders: type ref using index on customer_id; customers: type const using PRIMARY key

B orders: type eq_ref using PRIMARY key; customers: type ref using customer_id

C orders: type ALL scanning all rows; customers: type ALL scanning all rows

D orders: type index; customers: type index

💡 Hint

Check which table is filtered by primary key and which uses an index for the join.

The customers table is filtered by primary key (type const), and orders uses a ref type to find matching rows via the customer_id index.

📝 Syntax

advanced

Identify the syntax error in EXPLAIN usage

Which EXPLAIN statement is syntactically incorrect in MySQL?

A EXPLAIN SELECT * FROM users WHERE age > 30;

B EXPLAIN FORMAT=JSON SELECT * FROM users;

C EXPLAIN SELECT FORMAT JSON * FROM users;

D EXPLAIN EXTENDED SELECT * FROM users;

💡 Hint

Check the correct placement of FORMAT keyword in EXPLAIN syntax.

The FORMAT keyword must come immediately after EXPLAIN, not inside the SELECT clause.

❓ optimization

advanced

Optimizing a query based on EXPLAIN output

You run EXPLAIN on this query:

SELECT * FROM products WHERE category = 'Books' AND price < 20;

The output shows type: ALL and rows: 10000. What is the best way to improve performance?

A Remove the WHERE clause to scan fewer rows

B Add an index on price only

C Rewrite the query using OR instead of AND

D Add a composite index on (category, price)

💡 Hint

Think about how indexes can help filter multiple conditions efficiently.

A composite index on (category, price) allows MySQL to quickly filter rows matching both conditions, avoiding a full table scan.

🧠 Conceptual

expert

Understanding EXPLAIN JSON output details

In MySQL's EXPLAIN FORMAT=JSON output, which field indicates the estimated number of rows MySQL expects to examine for a table access?

A "filtered"

B "rows_examined_per_scan"

C "rows"

D "cost_info"

💡 Hint

Look for the field that quantifies rows scanned per index or table access.

"rows_examined_per_scan" shows the estimated number of rows MySQL expects to scan for that table access in the JSON EXPLAIN output.

Practice

1. What is the main purpose of using EXPLAIN before a SELECT query in MySQL?

easy

A. To create a backup of the database

B. To run the query faster by optimizing it automatically

C. To delete rows from the table before selecting

D. To see how MySQL plans to execute the query and find performance issues

Solution

1. Step 1: Understand the role of EXPLAIN

   EXPLAIN shows the execution plan MySQL uses for a query, helping to understand how tables and indexes are accessed.

2. Step 2: Identify what EXPLAIN does not do

   EXPLAIN does not run the query faster, delete data, or create backups; it only provides information about query execution.

3. Final Answer:

   To see how MySQL plans to execute the query and find performance issues -> Option D

4. Quick Check:

   EXPLAIN shows query plan = D [OK]

Hint: EXPLAIN shows query plan, not actual data changes [OK]

Common Mistakes:

• Thinking EXPLAIN runs the query faster
• Confusing EXPLAIN with data modification commands
• Assuming EXPLAIN creates backups

2. Which of the following is the correct syntax to use EXPLAIN with a SELECT query in MySQL?

easy

A. EXPLAIN * SELECT FROM users;

B. EXPLAIN SELECT * FROM users;

C. SELECT EXPLAIN * FROM users;

D. SELECT * FROM users EXPLAIN;

Solution

1. Step 1: Recall the correct EXPLAIN syntax

   The correct syntax is to write EXPLAIN before the SELECT statement: EXPLAIN SELECT ....

2. Step 2: Check each option

   EXPLAIN SELECT * FROM users; uses the correct syntax. Options A, B, and D place EXPLAIN incorrectly and will cause syntax errors.

3. Final Answer:

   EXPLAIN SELECT * FROM users; -> Option B

4. Quick Check:

   EXPLAIN before SELECT = C [OK]

Hint: Put EXPLAIN right before SELECT to check query plan [OK]

Common Mistakes:

• Placing EXPLAIN after SELECT or at the end
• Using EXPLAIN as a function inside SELECT
• Writing EXPLAIN in the wrong order

3. Given the table employees(id, name, department_id) with an index on department_id, what does the type column in EXPLAIN output usually show when the index is used?

medium

A. ALL

B. index

C. ref

D. const

Solution

1. Step 1: Understand EXPLAIN 'type' values

   The 'type' column shows how MySQL accesses rows. 'ALL' means full table scan, 'ref' means index lookup by reference, 'index' means full index scan, and 'const' means constant lookup.

2. Step 2: Identify index usage for a foreign key

   When searching by an indexed column like department_id, MySQL uses 'ref' to find matching rows efficiently.

3. Final Answer:

   ref -> Option C

4. Quick Check:

   Index lookup by reference = ref = B [OK]

Hint: 'ref' means index used for matching rows, not full scan [OK]

Common Mistakes:

• Confusing 'ALL' with index usage
• Thinking 'index' means index lookup by key
• Assuming 'const' applies to indexed foreign keys

4. You run EXPLAIN SELECT * FROM orders WHERE customer_id = 5; but notice the type column shows ALL. What is the most likely reason?

medium

A. There is no index on customer_id

B. The query syntax is incorrect

C. The table is empty

D. MySQL automatically ignores indexes

Solution

1. Step 1: Interpret 'ALL' in EXPLAIN output

   'ALL' means MySQL is scanning the entire table, which is slow and usually means no index is used.

2. Step 2: Identify cause of full table scan

   If the query filters by customer_id but no index exists on that column, MySQL must scan all rows.

3. Final Answer:

   There is no index on customer_id -> Option A

4. Quick Check:

   Full scan means missing index = A [OK]

Hint: 'ALL' means full scan, usually missing index on filter column [OK]

Common Mistakes:

• Assuming syntax error causes 'ALL'
• Thinking empty table causes 'ALL'
• Believing MySQL ignores indexes by default

5. You have a query joining products and categories tables on category_id. EXPLAIN shows type as ALL for products and ref for categories. What is the best way to improve performance?

hard

A. Add an index on products.category_id

B. Rewrite the query without JOIN

C. Add an index on categories.category_id

D. Use SELECT * instead of specific columns

Solution

1. Step 1: Analyze EXPLAIN output for join

   'ALL' on products means full scan; 'ref' on categories means index used there.

2. Step 2: Identify missing index

   Since products is scanned fully, adding an index on products.category_id helps MySQL find matching rows faster.

3. Step 3: Evaluate other options

   Rewriting query or adding index on categories.category_id won't help if products lacks index. SELECT * vs specific columns affects data size, not join efficiency.

4. Final Answer:

   Add an index on products.category_id -> Option A

5. Quick Check:

   Full scan means missing index on join column = A [OK]

Hint: Add index on table with 'ALL' type to speed up joins [OK]

Common Mistakes:

• Adding index on already indexed table
• Removing JOIN instead of indexing
• Ignoring EXPLAIN 'type' column meaning