EXPLAIN

Show the execution plan for a query (MySQL, PostgreSQL).

EXPLAIN SELECT ...;

Run the query and show the execution plan with actual statistics (PostgreSQL / MySQL 8.0.18 and later).

EXPLAIN ANALYZE SELECT ...;

Show the execution plan in JSON format (MySQL).

EXPLAIN FORMAT = JSON SELECT ...;

The following examples use the members table.

Check the basic execution plan.

sample_explain.sql

EXPLAIN
SELECT name, email
FROM members
WHERE email = 'user_a@example.com';

+----+-------------+---------+-------+--------------------------+--------------------------+---------+-------+------+-------+
| 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 |       |
+----+-------------+---------+-------+--------------------------+--------------------------+---------+-------+------+-------+
1 row in set

Check the execution plan for a query with a subquery.

sample_explain.sql

EXPLAIN
SELECT name, email
FROM members
WHERE member_id IN (SELECT member_id FROM members WHERE status = 'active');

+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
|  1 | PRIMARY     | members | ALL   | NULL          | NULL    | NULL    | NULL |    3 | Using where |
|  2 | SUBQUERY    | members | index | NULL          | PRIMARY |       4 | NULL |    3 | Using where |
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
2 rows in set

Check the detailed execution plan in JSON format (MySQL).

sample_explain.sql

EXPLAIN FORMAT = JSON
SELECT * FROM members WHERE status = 'active';

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "0.45"
    },
    "table": {
      "table_name": "members",
      "access_type": "ALL",
      "rows_examined_per_scan": 3,
      "rows_produced_per_join": 3,
      "filtered": "66.67",
      "cost_info": {
        "read_cost": "0.25",
        "eval_cost": "0.30",
        "prefix_cost": "0.55",
        "data_read_per_join": "8K"
      },
      "used_columns": ["member_id", "name", "email", "status"],
      "attached_condition": "(`members`.`status` = 'active')"
    }
  }
}
1 row in set

Show the execution plan along with actual runtime statistics (MySQL 8.0.18 and later).

sample_explain.sql

EXPLAIN ANALYZE
SELECT status, COUNT(*) AS cnt
FROM members
GROUP BY status;

-> Table scan on members  (cost=0.55 rows=3) (actual time=0.028..0.035 rows=3 loops=1)
-> Group aggregate: count(0)  (cost=0.85 rows=3) (actual time=0.042..0.058 rows=3 loops=1)
1 row in set

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 = 'user_a@example.com';

-- Run the query and collect actual statistics (PostgreSQL).
EXPLAIN ANALYZE SELECT name, email FROM members WHERE email = 'user_a@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 = 'user_a@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 = 'user_a@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 = 'user_a@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.