SQL Execution Plans
Understanding How SQL Queries Are Executed

Introduction

Imagine asking someone to fetch a book from the school library. Do they start from shelf 1 or go directly to the right rack? Similarly, when you run a SQL query, the database decides how to execute it — step by step. This step-by-step breakdown is called an Execution Plan.

What is an Execution Plan?

An execution plan is a visual or tabular representation of how the database will execute your SQL query. It shows the order of operations, access methods (like full table scan or index lookup), and how rows are filtered or joined.

Why It Matters

• Helps you spot performance bottlenecks
• Shows if indexes are used properly
• Guides query rewriting and optimization

How to View an Execution Plan

Use the EXPLAIN keyword:

EXPLAIN SELECT name, marks FROM students WHERE roll_no = 3;

Understanding EXPLAIN Output (MySQL)

id | select_type | table   | type  | key         | rows | Extra
---+-------------+---------+-------+-------------+------+----------------------------
1  | SIMPLE      | students| const | PRIMARY     | 1    | Using index

Key Columns Explained:

• id – step order in the query plan
• select_type – type of query (simple, subquery, etc.)
• table – table being read
• type – access type (const, ref, ALL, etc.)
• key – index being used (if any)
• rows – estimated rows scanned
• Extra – additional info like “Using where”, “Using index”

Types of Access (from best to worst)

Access Type	Description
const	Single row by primary key
ref	Uses index to find matching rows
range	Index is used for a range of values
ALL	Full table scan (slowest)

Example: Query Without Index

-- Create table and insert data
CREATE TABLE students (
  roll_no INT,
  name VARCHAR(50),
  class VARCHAR(10),
  marks INT
);

INSERT INTO students VALUES (1, 'Aarav Sharma', '10A', 85), (2, 'Diya Iyer', '10A', 92), (3, 'Mehul Agarwal', '10B', 78);

-- Query
EXPLAIN SELECT * FROM students WHERE name = 'Mehul Agarwal';

type: ALL    -- full table scan
key: NULL

Issue: No index on name → slow as data grows.

Example: Add Index and Improve

CREATE INDEX idx_name ON students(name);

EXPLAIN SELECT * FROM students WHERE name = 'Mehul Agarwal';

type: ref
key: idx_name

Improved performance! Index lookup is used.

Multi-Table Example: Join with EXPLAIN

-- Assume marks table exists
EXPLAIN SELECT s.name, m.subject, m.marks
FROM students s
JOIN marks m ON s.roll_no = m.roll_no
WHERE s.class = '10A';

table | type | key        | rows
------+-------+------------+-------
s     | ref  | idx_class  | 10
m     | ref  | roll_no_fk | 10

This tells you whether join fields are indexed properly and how many rows are scanned.

Best Practices

• Run EXPLAIN before and after optimization
• Index foreign keys and WHERE conditions
• Avoid full table scans unless needed
• Use LIMIT to restrict large fetches

Real-World School Use Cases

• Check how efficiently student search queries run
• Diagnose slow exam report generation
• Improve dashboard performance using indexes + EXPLAIN

Summary

An execution plan is your query’s story — how the database reads, filters, and joins data behind the scenes. By using EXPLAIN, you can make that story faster and more efficient — which is crucial when you're handling thousands of student records in real-world school systems.

What’s Next?

Coming up: SQL Interview Questions — prepare confidently with real-world SQL challenges and answers.