SQL EXPLAIN and Execution Plans

The last lesson said an index makes a lookup faster, an execution plan is how to actually confirm whether a given query is using one at all, instead of assuming it is.

Learning Objectives

• Explain what an execution plan is, and get one for a given query on a specific database.
• Read a plan well enough to tell an index-based access apart from a full table scan.
• Know the real EXPLAIN syntax split across all six databases, since none of them share one standard statement for it.

What an Execution Plan Shows

Before running a query, the database's optimizer picks an access method for each table involved (a full scan, or a specific index), a join algorithm and order if more than one table is involved, and a relative cost estimate for each step based on table statistics. An execution plan is that chosen plan, made visible, in most cases without even running the query for real.

The Real EXPLAIN Syntax, by Database

Getting a Plan for the Same Query

EXPLAIN
SELECT * FROM employees WHERE department_id = 3;

EXPLAIN
SELECT * FROM employees WHERE department_id = 3;

EXPLAIN QUERY PLAN
SELECT * FROM employees WHERE department_id = 3;

SET SHOWPLAN_ALL ON;
SELECT * FROM employees WHERE department_id = 3;

EXPLAIN PLAN FOR
SELECT * FROM employees WHERE department_id = 3;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Reading a Plan: Scan vs Index, by Database

Every database's plan answers the same question, full scan or index access, but each one names that answer differently.

Try It Yourself

Get an execution plan for a query filtering employees by last_name on MySQL, and decide whether an index on last_name would change the plan's access type.

EXPLAIN
SELECT * FROM employees WHERE last_name = 'Nguyen';
-- type: ALL means a full scan, department_id has an index from an
-- earlier lesson, last_name doesn't yet, so this is expected to read ALL.

Frequently Asked Questions

Common Beginner Mistakes

Best Practices

• Check the actual plan before assuming an index is being used, don't just assume it.
• On PostgreSQL and MySQL, run a destructive statement's EXPLAIN ANALYZE inside a transaction you roll back, since it genuinely executes.
• Compare plans before and after adding or removing an index on the real table size, an optimizer can skip an index entirely on a tiny test table since a full scan is already cheap there.

Interview Questions

Summary

An execution plan shows exactly how a query is actually going to run, or did run, scan type, index usage, join order, and cost. Getting one is genuinely different SQL on every one of these six databases, from a single EXPLAIN keyword to SQL Server's complete absence of one, and each database names "full scan" and "using an index" with its own distinct plan terminology.

What's Next?

The last lesson in this module ties indexes and execution plans together into practical habits: which columns are actually worth indexing, and the query patterns that quietly defeat an index that already exists.