What is EXPLAIN statement in MySQL and what it does ?

The EXPLAIN statement in MySQL is a powerful tool that provides insight into how MySQL executes a query. In this guide, we will talk about EXPLAIN and how to use it in MySQL, including practical examples.

What is the EXPLAIN Statement?

EXPLAIN is a MySQL statement used to obtain a query execution plan, which shows how MySQL will execute a query. It displays information about the query execution path, such as which indexes are used, how tables are joined, and how data is accessed.

Why Use EXPLAIN?

Using EXPLAIN helps in:

  1. Identifying inefficient queries.
  2. Understanding how MySQL processes a query.
  3. Optimizing query performance by tweaking indexes and query structures.

Basic Usage of EXPLAIN

To use EXPLAIN, simply prepend it to any SELECT query.

Example 1: Basic EXPLAIN

Suppose we have a table named Employees.

Query:

SQL
SELECT * FROM Employees WHERE Department = 'Sales';


Using EXPLAIN:

SQL
EXPLAIN SELECT * FROM Employees WHERE Department = 'Sales';

This will show how MySQL plans to execute the query, including which indexes, if any, it will use.

Understanding EXPLAIN Output

The output of EXPLAIN includes several columns, each providing valuable information:

  1. id: The sequence number of the SELECT within the query.
  2. select_type: The type of SELECT (e.g., SIMPLE, SUBQUERY).
  3. table: The table referred to by the row.
  4. type: The join type (e.g., ALL, index, range).
  5. possible_keys: Which indexes MySQL can choose from for this query.
  6. key: The index MySQL decided to use.
  7. rows: Estimated number of rows to be examined.
  8. Extra: Additional information about how MySQL will execute the query.

Example 2: Understanding EXPLAIN Output

Consider the following EXPLAIN output for the previous query:

Code
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | Employees | ALL  | NULL          | NULL | NULL    | NULL | 1000 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
  • type = ALL: Indicates a full table scan, which can be inefficient for large tables.
  • possible_keys = NULL: No potential indexes are considered.
  • Extra = Using where: Shows that a WHERE clause is used to filter the results.

Using EXPLAIN with Joins

EXPLAIN is also incredibly useful for queries involving joins, as it shows how MySQL joins tables.

Example 3: EXPLAIN with a Join

Assume we have another table, Departments, and we perform a join with Employees.

Query:

SQL
SELECT Employees.Name, Departments.DepartmentName FROM Employees JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;


Using EXPLAIN:

SQL
EXPLAIN SELECT Employees.Name, Departments.DepartmentName FROM Employees JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

This EXPLAIN statement will reveal how the join is executed, including which indexes are utilized for the join conditions.