Master MySQL EXPLAIN: Decode Execution Plans to Boost Query Performance

Background

In daily work we often encounter slow‑running queries; the slow‑query log helps locate them, but merely finding long‑running SQL does not solve the problem. Using the EXPLAIN command reveals the execution plan, allowing us to verify whether indexes are used effectively.

2 EXPLAIN Core Functions

EXPLAIN is MySQL's SQL performance microscope that simulates the optimizer and outputs key information:

id : determines the order of table reads and join types.

type : shows the data access method (e.g., full table scan, index lookup).

possible_keys / key : indicate which indexes could be used and which one is actually chosen.

Extra : provides detailed execution hints.

rows : estimated number of rows examined.

filtered : estimated percentage of rows that pass the WHERE filter.

3 Execution Plan Field Details

3.1 id – Execution Priority

For single‑table queries all rows share the same id . In multi‑table joins a larger id means higher priority (MySQL 5.6+ uses auto‑increment). Identical id values indicate tables at the same level; different values indicate subqueries or derived tables, which should be examined together with select_type .

3.2 select_type – Query Type Marker

Indicates the type of each SELECT clause, such as SIMPLE , PRIMARY , SUBQUERY , DERIVED , UNION , DEPENDENT SUBQUERY , etc., each with different performance implications.

3.3 type – Data Access Method (Performance Key)

From worst to best, the access types are:

ALL – full table scan (must be optimized).

index – full index scan.

range – index range scan (BETWEEN/IN/LIKE).

ref – non‑unique index lookup.

eq_ref – unique index lookup (ideal).

const – primary‑key equality (single row).

system – system table check (no optimization needed).

NULL – optimizer resolves the expression without accessing tables.

3.4 possible_keys & key – Index Decision Analysis

possible_keys : set of indexes the optimizer may choose.

key : the index actually used.

key_len : length of the index in bytes (helps verify index utilization).

3.5 rows – Estimated Scanned Rows

Shows how many rows MySQL expects to examine; smaller numbers are better, but must be considered together with filtered .

3.6 filtered – Condition Filter Rate

Represents the percentage of rows that satisfy the WHERE clause; the product rows × filtered approximates the actual result row count.

3.7 Extra – Additional Information (Performance Pitfalls)

Using filesort – requires extra sorting; add a composite index covering the ORDER BY columns.

Using temporary – creates a temporary table for GROUP BY/ORDER BY; rewrite the query to avoid it.

Using index – indicates a covering index (ideal).

Using where – means rows are filtered after index lookup; ensure the index covers the filter.

Select tables optimized away – optimizer returns a constant (no action needed).

4 Practical Execution Plan Analysis

Case 1: Full Table Scan Optimization

<code>EXPLAIN SELECT * FROM orders WHERE customer_id = 1001;</code>

Original output shows type: ALL , rows: 1000000 , and Extra: Using where . Adding an index on customer_id changes the plan to type: ref, rows: 10, key: idx_customer_id .

Case 2: Index Failure Diagnosis

<code>EXPLAIN SELECT * FROM products WHERE name LIKE '%MySQL%';</code>

The plan reports type: ALL and possible_keys: idx_name , indicating the leading wildcard prevents index usage. Solutions: use a FULLTEXT index or rewrite the pattern to LIKE 'MySQL%' .

Case 3: Join Query Optimization

<code>EXPLAIN SELECT o.*, c.name FROM orders o JOIN customers c ON o.customer_id = c.id WHERE c.country = 'CN';</code>

Key observations: type: eq_ref (primary‑key join), Extra: Using index (covering index), and rows: 1 (optimal).

5 Advanced Diagnostic Techniques

5.1 Force Index Verification

<code>EXPLAIN SELECT * FROM products FORCE INDEX (idx_price) WHERE price > 100;</code>

5.2 Formatted EXPLAIN Output

<code>EXPLAIN FORMAT=JSON SELECT ... -- obtain structured execution plan</code>

5.3 Optimizer Trace

<code>SET optimizer_trace='enabled=on';
SELECT ...;
SELECT * FROM information_schema.OPTIMIZER_TRACE;</code>

6 Summary of Diagnostic Process

Observe the type field – avoid ALL or index when possible.

Check the key field – ensure the intended index is used.

Analyze the Extra field – eliminate Using filesort and Using temporary warnings.

Validate the rows value – confirm the estimated scan size is reasonable.

By systematically analyzing these core fields, developers can pinpoint SQL performance bottlenecks, guide index optimization, and rewrite queries for better efficiency, especially when combined with slow‑query logs for comprehensive tuning.