Master MySQL Performance: Using EXPLAIN & EXPLAIN ANALYZE to Optimize Queries

What Is EXPLAIN and EXPLAIN ANALYZE?

EXPLAIN is a MySQL keyword that shows how the optimizer will execute a query and estimates its cost. EXPLAIN ANALYZE, introduced in MySQL 8.0.18, actually runs the query and prints the real cost of each iterator.

Key Columns in EXPLAIN Output

id : identifies the SELECT to which the row belongs.

select_type : indicates whether the SELECT is simple, primary (complex), subquery, UNION, or DELETE.

table : the table accessed at each step.

partition : partitions used (if the table is partitioned).

type : the access type (e.g., const, ref, range, all).

possible_keys : indexes MySQL could use.

key : the index actually chosen.

key_len : length of the chosen index (important for composite indexes).

rows : estimated number of rows examined.

filtered : estimated percentage of rows that satisfy the filter condition.

Extra : additional information such as Using index, Using where, Using temporary, etc.

When to Use EXPLAIN vs. EXPLAIN ANALYZE

Use EXPLAIN when you are unsure whether a query is efficient. If the query is already indexed and partitioned but still runs slowly, switch to EXPLAIN ANALYZE to see actual execution costs. Remember that EXPLAIN ANALYZE runs the query, so avoid it on production data if side‑effects are a concern.

Important Index‑Related Columns

possible_keys : all candidate indexes.

key : the index actually used.

key_len : length of the used index, crucial for composite indexes.

FULLTEXT Indexes and Joins

If a query uses a FULLTEXT index in a JOIN, the select_type column will show FULLTEXT.

Partitions

When a table is partitioned, the partition column lists the partitions accessed. You can force specific partitions with syntax such as SELECT * FROM table_name PARTITION(p1,p2).

Limitations of EXPLAIN

It does not account for triggers, stored functions, or UDFs.

It cannot analyze stored procedures.

It does not show the optimizer’s internal decision process.

Many statistics are estimates and may be inaccurate.

Some values (e.g., filesort vs. Using temporary) are ambiguous.

If EXPLAIN fails to parse a query, you can run SHOW WARNINGS; to get diagnostic hints.

EXPLAIN’s EXTRA Column Values

Using index – a covering index avoids table access.

Using where – rows are filtered after being read from the storage engine.

Using temporary – MySQL creates a temporary table for sorting.

Using filesort – external sort is used instead of index order.

Range checked for each record – indicates missing suitable index.

Using index condition – index condition pushdown before table read.

Backward index scan – descending index scan.

const row not found – the table is empty.

Using index for group‑by – index helps GROUP BY.

Connection Types (type column)

NULL – query resolved at optimization time, no table access.

system – table has zero or one row.

const – matches at most one row (primary key or unique index).

eq_ref – uses a unique index (primary or NOT NULL unique).

ref – uses a non‑unique index with equality comparison.

fulltext – joins using a FULLTEXT index.

index – full index scan (no sorting needed).

range – index range scan.

all – full table scan.

index_merge – combines multiple indexes.

unique_subquery / index_subquery – subquery optimizations.

Practical Optimization Example

Step 1: Run the Initial Query

Using the MySQL sample employees database, run a query that filters by last name and first name:

SELECT * FROM employees WHERE last_name = 'Puppo' AND first_name = 'Kendra';

EXPLAIN shows that the query scans 299,733 rows, indicating a performance problem.

Step 2: Optimization Method 1 – Separate Indexes

Creating separate indexes on last_name and first_name allows MySQL to find rows for each column individually, but it cannot efficiently locate the combination last_name='Puppo' AND first_name='Kendra'.

MySQL may use an index‑merge, but this is often less efficient than a single composite index.

Step 3: Optimization Method 2 – Multi‑Column Index

Define a composite index covering both columns:

CREATE INDEX fullnames ON employees(last_name, first_name);

Now run the same SELECT and check with EXPLAIN:

The optimized plan uses the new composite index and accesses only one row, a dramatic improvement over the original 299,733 rows.

Conclusion

MySQL’s EXPLAIN (and EXPLAIN ANALYZE) provide essential insight into how queries are executed. By interpreting the output, choosing appropriate indexes—especially composite indexes—and verifying plans with EXPLAIN, you can significantly boost database performance.