EXPLAIN plan for query analysis in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What does this EXPLAIN output indicate about the query?

Given the EXPLAIN output below for a SELECT query, what does it tell you about how the database will execute the query?

EXPLAIN SELECT * FROM employees WHERE department_id = 5;

SQL

id | select_type | table     | type  | possible_keys | key         | key_len | ref   | rows | Extra
1  | SIMPLE      | employees | ref   | department_id | department_id | 4       | const | 10   | Using where

A The query performs a full join between employees and another table.

B The query will scan the entire employees table without using any index.

C The query uses a temporary table and filesort to order results.

D The query uses an index on department_id to find matching rows efficiently.

💡 Hint

Look at the 'key' column to see if an index is used.

The 'key' column shows 'department_id', meaning the query uses the index on department_id. The 'type' is 'ref', indicating an index lookup, not a full scan.

🧠 Conceptual

intermediate

What is the main purpose of using EXPLAIN in SQL?

Why do database developers use the EXPLAIN command before running complex queries?

A To see the query results before execution.

B To understand how the database will execute the query and optimize it.

C To create indexes automatically for the query.

D To delete duplicate rows from the result.

💡 Hint

Think about how EXPLAIN helps with performance.

EXPLAIN shows the query plan, helping developers see how tables are accessed and if indexes are used, so they can improve query speed.

📝 Syntax

advanced

Which EXPLAIN syntax is correct for analyzing a SELECT query in PostgreSQL?

Choose the correct syntax to get the execution plan for a SELECT query in PostgreSQL.

SQL

SELECT * FROM orders WHERE order_date > '2023-01-01';

A EXPLAIN PLAN FOR SELECT * FROM orders WHERE order_date > '2023-01-01';

B EXPLAIN QUERY SELECT * FROM orders WHERE order_date > '2023-01-01';

C EXPLAIN SELECT * FROM orders WHERE order_date > '2023-01-01';

D EXPLAIN EXECUTE SELECT * FROM orders WHERE order_date > '2023-01-01';

💡 Hint

PostgreSQL uses a simple EXPLAIN keyword before the query.

In PostgreSQL, the correct syntax is 'EXPLAIN' followed by the query. Other options are invalid or from other database systems.

❓ optimization

advanced

How can EXPLAIN help improve a slow query with multiple joins?

You have a slow query joining three large tables. How does EXPLAIN help you improve it?

A It shows which join order and indexes the database uses, helping you adjust indexes or rewrite the query.

B It automatically rewrites the query to be faster.

C It deletes unnecessary rows from the tables to speed up the query.

D It caches the query results for faster future runs.

💡 Hint

EXPLAIN shows the plan but does not change the query.

EXPLAIN reveals the join order, index usage, and scan types, so you can add indexes or rewrite joins to improve speed.

🔧 Debug

expert

What error or issue does this EXPLAIN output reveal about the query?

EXPLAIN output for a query shows:

id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra 1 | SIMPLE | orders | ALL | NULL | NULL | NULL | NULL | 10000 | Using where

What does this indicate?

A The query uses a full table scan on orders, which may cause slow performance.

B The query uses an index to quickly find matching rows.

C The query is using a temporary table and filesort.

D The query has a syntax error and will not run.

💡 Hint

Look at the 'type' and 'key' columns.

The 'type' is 'ALL' and 'key' is NULL, meaning no index is used and the entire table is scanned, which can be slow for large tables.