SQL Execution Plans — Understanding How SQL Queries Are Executed

The office creates an index, yet a report remains slow. The plan can show whether the optimizer uses that index and where work is expected.

An execution plan describes the database’s chosen or proposed access strategy for a statement. Estimated counts are model predictions, not necessarily measured execution results.

MySQL EXPLAIN reports access choices and estimates. EXPLAIN ANALYZE executes supported statements and adds actual timing and row information. SQLite EXPLAIN QUERY PLAN has a different display. Read plan output in the context of the engine, statistics and dataset rather than treating a screenshot as a universal answer.

Related concepts: Indexes and Tune SQL Performance.

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.

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

SQL · portable concepts; preserved examples identify their engine

Use the EXPLAIN keyword:

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

SQL · portable concepts; preserved examples identify their engine

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”

SQL · portable concepts; preserved examples identify their engine

The access types below are a useful vocabulary, not an absolute fastest-to-slowest ranking. Cost depends on selectivity, row counts, covering behavior and the workload; a scan can be appropriate.

SQL · portable concepts; preserved examples identify their engine

-- 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.

SQL · portable concepts; preserved examples identify their engine

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.

SQL · portable concepts; preserved examples identify their engine

-- 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.

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

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

Plan choices and formatting can change with data, statistics and engine versions. A full scan can be sensible for a small table. EXPLAIN ANALYZE is real execution, so use it only where the supported statement and environment are appropriate.

Read the report below, then inspect its plan on your installed engine. Predict why a three-row table may reasonably be scanned.

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

SQL · this practice variation runs with SQLite 3; the preserved examples retain their stated dialect

SELECT name FROM practice_students WHERE city = 'Pune' ORDER BY roll_no;

The logical result has two names. The exercise intentionally does not promise a particular plan or runtime; use EXPLAIN on this query to observe your engine’s choice.

An execution plan describes the database’s chosen or proposed access strategy for a statement. Estimated counts are model predictions, not necessarily measured execution results.

The logical result has two names. The exercise intentionally does not promise a particular plan or runtime; use EXPLAIN on this query to observe your engine’s choice.

Continue your understanding with Indexes and Tune SQL Performance.

Source for the engine-specific behavior: MySQL 8.4 reference documentation.