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.