EXPLAIN

MySQL

Example

EXPLAIN SELECT * FROM students;

Output

+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | students  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+

Explanation

The EXPLAIN statement in SQL is used to obtain a query execution plan, or a description of how MySQL would execute a given query. The query in the example is asking for all entries in the ‘students’ table. The output describes the steps the MySQL optimizer would take to execute this query. The ‘type’ column of the output specifies the join type, and ‘ALL’ indicates a full table scan which could impact performance for larger tables.

PostgreSQL

Example

In SQL markdown format:

EXPLAIN SELECT * FROM employees

Output

In a markdown format:

Seq Scan on employees  (cost=0.00..37.50 rows=2550 width=36)

Explanation

The EXPLAIN keyword in PostgreSQL is used to provide a query execution plan of a statement. In this case, EXPLAIN SELECT * FROM employees generates a high-level query execution plan for retrieving data from the employees table. The output Seq Scan on employees (cost=0.00..37.50 rows=2550 width=36) signifies a sequential scan performed on the employees table with an estimated cost of 0.00 to 37.50, approximately 2550 rows to scan, each row with a size of 36 bytes.

SQL Server

Example

EXPLAIN SELECT * FROM Employees;

Output

+-----------+---------+-------+----------------+-------------+
| id        | select_type | table  | type   | possible_keys |
+-----------+---------+-------+----------------+-------------+
| 1         | SIMPLE | Employees | ALL    | NULL          |
+-----------+---------+-------+----------------+-------------+

Explanation

In the provided example, EXPLAIN SELECT * FROM Employees; is used to get information about how SQL Server would execute the given query. The output table provides an insight into query performance. Here, the ‘type’ column indicates how the table is scanned when executing the query and ‘ALL’ signifies a full table scan, which might not be efficient for large tables.

Oracle

Example

EXPLAIN PLAN FOR
SELECT * FROM employees;

Output

----------------------------------------------------------
 Plan hash value: 1601196873

-----------------------------------------------------------
| Operation         | Name    | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------
| SELECT STATEMENT  |         |   107 |  2675 |     3   (0)|
|  TABLE ACCESS FULL| EMP     |   107 |  2675 |     3   (0)|
-----------------------------------------------------------

Explanation

The EXPLAIN PLAN statement in Oracle is used to determine the execution plan that the Oracle optimizer would choose to execute a specified SQL statement. In the presented example, the EXPLAIN PLAN statement was used on a SQL statement which selects all records from the employees table.

The output shows that the full table scan operation (TABLE ACCESS FULL) is used to retrieve the data. The row and byte estimates, along with cost values, can also be seen. This information can be used to improve query performance if necessary.

SQLite

Example

EXPLAIN QUERY PLAN SELECT * FROM users WHERE user_id = 1;

Output

QUERY PLAN
`--SCAN TABLE users

Explanation

The EXPLAIN QUERY PLAN command in SQLite displays the query execution plan. The plan shows a SCAN TABLE users meaning that the database would need to scan the entire users table to execute this query, since no index was specified in this case.