Master MySQL Index Optimization with EXPLAIN: A Practical Guide

Why Slow Queries Matter

In internet companies, growing user and data volumes inevitably lead to slow queries, which cause delayed responses, timeouts, and even database connection exhaustion under high concurrency, making services unavailable.

Index Optimization Steps

The most effective way to resolve slow‑query problems is to optimize indexes. The typical workflow includes:

Run EXPLAIN to view the execution plan.

Identify improperly used indexes.

Refine the SQL until the optimal index usage is achieved.

Using EXPLAIN to Analyze Queries

Adding the EXPLAIN keyword before a statement shows MySQL's optimizer plan, revealing which tables and indexes are used, their order, and the type of each operation.

EXPLAIN SELECT * FROM test1;

id Column

The id column indicates the order in which tables are processed. When IDs are identical, tables are executed from top to bottom; when IDs differ, the larger ID is executed first (bottom‑up).

select_type Column

This column describes the type of SELECT, such as SIMPLE , PRIMARY , SUBQUERY , DERIVED , UNION , and UNION RESULT . The most common types are SIMPLE, PRIMARY, SUBQUERY, and DERIVED.

table Column

Shows the name of the table referenced by each row (e.g., test1, test2) or special markers like <derivedN> for derived tables.

partitions Column

Indicates which partitions are examined for the query.

type Column

Represents the join type, ordered from best to worst: system > const > eq_ref > ref > range > index > ALL . Understanding these helps gauge index effectiveness.

possible_keys Column

Lists indexes that could be used for the query. If NULL, no relevant index exists.

key Column

Shows the actual index employed. It may be non‑NULL even when possible_keys is NULL.

key_len Column

Indicates the length of the index used, reflecting how fully the index is applied. Factors include character set, column length, and nullability.

ref Column

Displays the column or constant that the index matches.

rows Column

Estimates the number of rows MySQL expects to examine.

filtered Column

Estimates the percentage of rows filtered by table conditions; lower values mean more filtering.

Extra Column

Provides additional information such as Using filesort, Using index, Using temporary, Using where, or Impossible WHERE, which help identify performance bottlenecks.

Practical Examples

Below are several EXPLAIN demonstrations with their results.

EXPLAIN SELECT * FROM test1;

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

EXPLAIN SELECT * FROM test2 WHERE code='001';

EXPLAIN SELECT code FROM test1 ORDER BY name DESC;

Optimization Workflow

Identify slow queries via the slow‑query log.

Run EXPLAIN to examine index usage.

Focus on key , key_len , type , and Extra columns to spot issues.

Adjust the SQL or indexes based on the findings.

Repeat the analysis until performance improves.