EXPLAIN

-- Show the execution plan for a query (MySQL, PostgreSQL).
EXPLAIN SELECT ...;

-- Run the query and show the execution plan with actual statistics (PostgreSQL).
EXPLAIN ANALYZE SELECT ...;

-- Run the query and show detailed statistics (MySQL).
EXPLAIN ANALYZE SELECT ...;

-- Show the execution plan in JSON format (MySQL).
EXPLAIN FORMAT = JSON SELECT ...;

Column	Description
id	The SELECT identifier within the query. Multiple rows appear when subqueries are present.
select_type	The type of SELECT. Common values include SIMPLE (no subqueries), SUBQUERY, and DERIVED (subquery in FROM clause).
table	The table referenced in this step.
type	The access method used for the table. Efficiency decreases in this order: const, ref, range, index, ALL.
possible_keys	The indexes that could potentially be used.
key	The index actually used. NULL means a full table scan is performed.
rows	The estimated number of rows to scan. A smaller value means better efficiency.
Extra	Additional information. Common values include Using index (covering index) and Using filesort (sort operation).

-- Check the basic execution plan.
EXPLAIN
SELECT name, email
FROM members
WHERE email = 'tanaka@example.com';

-- Check the execution plan for a query with a JOIN.
EXPLAIN
SELECT m.name, o.total
FROM members m
JOIN orders o ON m.member_id = o.member_id
WHERE m.status = 'active';

-- Check the detailed execution plan in JSON format (MySQL).
EXPLAIN FORMAT = JSON
SELECT * FROM orders WHERE ordered_at >= '2025-01-01';

-- Show the execution plan along with actual runtime statistics (MySQL 8.0.18 and later).
EXPLAIN ANALYZE
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

-- Result of: EXPLAIN SELECT name, email FROM members WHERE email = 'tanaka@example.com';
-- When an index exists:
-- +----+-------------+---------+------+-------------------------+-------------------------+---------+-------+------+-------+
-- | id | select_type | table   | type | possible_keys           | key                     | key_len | ref   | rows | Extra |
-- +----+-------------+---------+------+-------------------------+-------------------------+---------+-------+------+-------+
-- |  1 | SIMPLE      | members | const| idx_members_email_unique| idx_members_email_unique| 1022    | const |    1 |       |
-- +----+-------------+---------+------+-------------------------+-------------------------+---------+-------+------+-------+

-- When no index exists (type is ALL = full table scan):
-- +----+-------------+---------+------+---------------+------+---------+------+------+-------------+
-- | id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra       |
-- +----+-------------+---------+------+---------------+------+---------+------+------+-------------+
-- |  1 | SIMPLE      | members | ALL  | NULL          | NULL | NULL    | NULL | 5000 | Using where |
-- +----+-------------+---------+------+---------------+------+---------+------+------+-------------+

PostgreSQL supports both EXPLAIN and EXPLAIN ANALYZE, which actually executes the query and returns real runtime statistics.

-- Check the execution plan (PostgreSQL).
EXPLAIN SELECT name, email FROM members WHERE email = 'tanaka@example.com';

-- Run the query and collect actual statistics (PostgreSQL).
EXPLAIN ANALYZE SELECT name, email FROM members WHERE email = 'tanaka@example.com';

Oracle uses EXPLAIN PLAN FOR to save the execution plan to PLAN_TABLE, then DBMS_XPLAN.DISPLAY to view it.

-- Save the execution plan (Oracle).
EXPLAIN PLAN FOR
SELECT name, email FROM members WHERE email = 'tanaka@example.com';

-- Display the saved execution plan (Oracle).
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SQL Server uses SET SHOWPLAN_ALL ON or the "Include Actual Execution Plan" option. Set it before running the query to view the estimated plan only.

-- Show the estimated execution plan (SQL Server).
SET SHOWPLAN_ALL ON;
GO
SELECT name, email FROM members WHERE email = 'tanaka@example.com';
GO
SET SHOWPLAN_ALL OFF;

SQLite supports EXPLAIN QUERY PLAN for a simplified view of the execution plan.

-- Check the execution plan (SQLite).
EXPLAIN QUERY PLAN
SELECT name, email FROM members WHERE email = 'tanaka@example.com';

The most important columns in EXPLAIN output are type and key. When type is ALL, a full table scan occurs — reading every row in the table — which can severely impact performance on large tables. If the key column shows the intended index name, the index is being used.

The rows column shows the estimated number of rows scanned. For queries with JOINs, multiply the rows values across each table to get a rough estimate of total scan volume. A high value suggests adding an index or rewriting the query.

To create or drop indexes, see CREATE INDEX. Proper index design is the most effective way to improve query performance.