Master MySQL Index Optimization with EXPLAIN: A Step‑by‑Step Guide

Preface

In large‑scale internet applications, slow queries inevitably appear as user and data volumes grow, leading to delayed responses, timeouts, and even connection exhaustion under high concurrency.

To address these issues, the most effective technique is index optimization, which directly improves the execution plan of problematic SQL statements.

What is EXPLAIN?

MySQL’s official documentation states that EXPLAIN can be used with SELECT, DELETE, INSERT, REPLACE, and UPDATE statements. When paired with an executable statement, it shows the optimizer’s execution plan, including how tables are joined and in what order.

EXPLAIN Syntax

EXPLAIN [EXPLAIN_TYPE] {SELECT|DELETE|INSERT|REPLACE|UPDATE} ...

EXPLAIN_TYPE can be EXTENDED, PARTITIONS, or FORMAT=JSON, among others.

Using EXPLAIN

Running EXPLAIN SELECT * FROM test1; produces a result set with twelve columns that describe the execution plan. Understanding each column is essential for diagnosing index usage.

Key Output Columns

id : Execution order of tables; lower ids are processed first.

select_type : Type of SELECT (SIMPLE, PRIMARY, SUBQUERY, DERIVED, UNION, UNION RESULT, etc.).

table : Name of the table or derived source (e.g., <unionM,N>, <derivedN>, <subqueryN>).

type : Join type, ranging from best to worst (system > const > eq_ref > ref > range > index > ALL).

possible_keys : Indexes that could be used for the query.

key : The actual index chosen; may be NULL if no index applies.

key_len : Length of the used index portion, indicating how fully the index is utilized.

ref : Columns or constants used with the index.

rows : Estimated number of rows examined.

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

Extra : Additional information such as Using index, Using filesort, Using temporary, or Impossible WHERE.

Common Join Types and Examples

Examples illustrate how different type values appear:

system : Only one row in the table; rarely seen.

const : Index lookup returns a single row (e.g., primary key). EXPLAIN SELECT * FROM test2 WHERE id=1; eq_ref : Unique index lookup on each row of the outer table.

EXPLAIN SELECT * FROM test2 t1 INNER JOIN test2 t2 ON t1.id=t2.id;

ref : Non‑unique index lookup. EXPLAIN SELECT * FROM test2 WHERE code='001'; range : Range scans (BETWEEN, IN).

EXPLAIN SELECT * FROM test2 WHERE id BETWEEN 1 AND 2;

index : Full index scan. EXPLAIN SELECT code FROM test2; ALL : Full table scan.

EXPLAIN SELECT * FROM test2;

Index Length (key_len) Calculation

The key_len value depends on character set (bytes per character), column length, and nullability. For example, a VARCHAR(30) in UTF‑8 occupies 30 × 3 + 2 bytes; a CHAR(30) occupies 30 × 3 bytes, plus an extra byte if the column can be NULL.

Index Optimization Workflow

Identify slow queries via the slow‑query log.

Run EXPLAIN on the problematic SQL.

Focus on four columns: key (whether an index is used), key_len (index coverage), type (join efficiency), and Extra (additional warnings such as filesort or temporary tables).

Adjust the SQL or modify/create appropriate indexes based on the findings.

Repeat the EXPLAIN step to verify improvements.

Following this systematic approach usually reveals the root cause of index‑related performance issues and guides effective remediation.