EXPLAIN Statement

Introduction

In the last two lessons, you learned principles and techniques for writing faster queries — but how do you actually know whether a given query is following those techniques, or whether an index is even being used at all? Guessing is not good enough. MySQL provides a tool that answers this directly: EXPLAIN.

EXPLAIN is one of the most useful commands you will ever run against a MySQL database. It turns "I think this query is slow" into "here is exactly why, and here is exactly what to fix."

What Does EXPLAIN Do?

EXPLAIN shows you the execution plan MySQL intends to use for a query — which indexes (if any) it will use, in what order it will access tables, and roughly how many rows it expects to examine. Placed in front of a SELECT, it reports this plan back to you instead of running the query for its actual result set.

This makes EXPLAIN completely safe to run on any query, including ones that would normally return a huge number of rows — you get the plan back almost instantly, without waiting for (or being affected by) the full query.

Running EXPLAIN

Using EXPLAIN is as simple as adding the keyword in front of any SELECT statement.

EXPLAIN SELECT order_id, total
FROM orders
WHERE customer_id = 42;

+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1  | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | NULL | 8000 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+

This particular result shows a table with no useful index on customer_id yet — notice type is ALL and key is NULL. You will fix exactly this in the worked example further down.

Reading the Output

EXPLAIN's output has several columns, but four of them tell you almost everything you need for day-to-day tuning.

Full Table Scans

When type shows ALL, MySQL is reading every single row in the table to find the ones that match. On a small lookup table with a handful of rows, that is completely fine and often faster than using an index at all. On a table with millions of rows, a full table scan on a frequently-run query is a serious performance red flag.

Example: Before and After an Index

Here is the same query, run before and after adding an index on the column it filters by. Watch how type, key, and rows all change.

EXPLAIN SELECT order_id, total
FROM orders
WHERE customer_id = 42;

+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1  | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | NULL | 8000 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

EXPLAIN SELECT order_id, total
FROM orders
WHERE customer_id = 42;

+----+-------------+--------+------+------------------------+------------------------+---------+-------+------+-------+
| id | select_type | table  | type | possible_keys          | key                    | key_len | ref   | rows | Extra |
+----+-------------+--------+------+------------------------+------------------------+---------+-------+------+-------+
| 1  | SIMPLE      | orders | ref  | idx_orders_customer_id | idx_orders_customer_id | 4       | const | 2    | NULL  |
+----+-------------+--------+------+------------------------+------------------------+---------+-------+------+-------+

Before the index, MySQL had no choice but to scan all 8,000 rows (type: ALL). After the index, type becomes ref, key shows the new index is actually being used, and the rows estimate drops from 8,000 to just 2 — MySQL now jumps almost directly to the matching rows.

Common Mistakes

Key Takeaways

Summary

EXPLAIN turns query tuning from guesswork into evidence. Instead of assuming a query is slow (or fast), you can see precisely which indexes MySQL is using and roughly how much work it expects to do.

In this lesson, you learned what EXPLAIN does, how to run it, how to read its key output columns, and how adding an index visibly improves the plan. Next, you will start connecting MySQL to real application code, beginning with PHP.