Database.ExplainCommand

EXPLAIN in SQL

The EXPLAIN (or sometimes EXPLAIN PLAN) command is used to display the execution plan of a SQL query.

In simple terms:

• It shows how the database engine will execute your query.
• It helps understand and optimize query performance.
• It tells you about table scans, indexes used, join algorithms, estimated costs, and other internal operations.

Why Use EXPLAIN?

• To identify bottlenecks (e.g., full table scans instead of index usage).
• To understand join orders and types.
• To see if indexes are being used effectively.
• To predict query performance before running a heavy query.

Basic Example

Suppose you have this query:

SELECT name FROM employees WHERE department_id = 10;

You can see the execution plan like this (in MySQL):

EXPLAIN SELECT name FROM employees WHERE department_id = 10;

It will output something like:

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	employees	ref	dept_id_idx	dept_id_idx	5	Using index

Key columns:

• id: Query step identifier.
• select_type: Type of SELECT (e.g., SIMPLE, PRIMARY, SUBQUERY).
• table: Which table is being accessed.
• type: Join type (e.g., ALL (full scan), index, ref, eq_ref).
• possible_keys: Which indexes could be used.
• key: The index actually used.
• rows: Estimated number of rows to examine.
• Extra: Additional information like “Using index”, “Using temporary”, “Using filesort”.

Common Join Types (important for EXPLAIN)

• ALL — full table scan (bad for large tables).
• index — full index scan.
• range — index range scan (good for BETWEEN, <, >, etc.).
• ref — lookup by a non-unique index.
• eq_ref — lookup by a unique index (fast).
• const — single row constant lookup (super fast).

Different Databases

• MySQL/MariaDB: EXPLAIN SELECT ... or EXPLAIN FORMAT=JSON SELECT ...
• PostgreSQL: EXPLAIN SELECT ..., or EXPLAIN ANALYZE SELECT ... (runs the query and gives real timings)
• Oracle: EXPLAIN PLAN FOR SELECT ... then SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
• SQL Server: Uses Query Execution Plans via Management Studio or SET SHOWPLAN_ALL ON.

Practical Tip

• Use EXPLAIN ANALYZE (PostgreSQL) to not only see the plan but also real run times.
• If your type is ALL often, think about adding indexes or rewriting queries.
• Look for “Using where” and “Using index” (good) vs. “Using temporary”, “Using filesort” (bad for performance).

Short Summary

Feature	What it Does
What it shows	How your SQL query will be executed
Helps with	Finding slow parts of your query
Key benefits	Better performance tuning, smarter indexing
Important output	Table, type (join type), used keys, row estimates