Master MySQL Index Optimization: How EXPLAIN Reveals Hidden Performance Bottlenecks

For internet companies, growing user and data volumes inevitably lead to slow queries, which can cause timeouts, full connection pools, and service outages.

Key mitigation methods include monitoring SQL execution, enabling slow‑query logs, simplifying business logic, refactoring code, asynchronous processing, and especially index optimization, which is often the most effective solution.

To inspect a query's index usage, prepend the

EXPLAIN

keyword to the SQL statement; MySQL then returns an execution plan showing how tables and indexes are accessed.

EXPLAIN Overview

MySQL supports

EXPLAIN

for

SELECT

,

DELETE

,

INSERT

,

REPLACE

, and

UPDATE

statements. It displays optimizer‑generated information such as table join order, chosen indexes, and estimated row counts.

EXPLAIN Syntax

<code>EXPLAIN [EXTENDED|PARTITIONS|FORMAT=JSON] stmt;</code>

Example:

<code>EXPLAIN SELECT * FROM test1;</code>

The result includes twelve columns; each is explained below.

id Column

Indicates the execution order of tables. Same

id

values mean the tables are processed top‑to‑bottom; larger

id

values are processed first.

select_type Column

Describes the type of SELECT. Common values:

SIMPLE – a straightforward SELECT without subqueries or UNION.

PRIMARY – the outermost query in a complex statement.

SUBQUERY – a SELECT appearing in a WHERE or SELECT list.

DERIVED – a subquery in the FROM clause (derived table).

UNION – a SELECT after a UNION keyword.

UNION RESULT – the combined result set of a UNION.

table Column

Shows the table name referenced by each row (e.g.,

test1

,

test2

) or special identifiers such as

<derivedN>

,

<unionM,N>

, and

<subqueryN>

.

type Column

Indicates the join type, ordered from best to worst:

system

>

const

>

eq_ref

>

ref

>

range

>

index

>

ALL

. Common cases:

system : table has only one row.

const : a primary‑key or unique index matches a constant.

eq_ref : each row in the outer table matches at most one row in the inner table via a primary or unique index.

ref : non‑unique index lookup.

range : range scan (e.g., BETWEEN, IN).

index : full index scan.

ALL : full table scan.

possible_keys Column

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

key Column

Shows the actual index chosen. It may be non‑NULL even when

possible_keys

is NULL.

key_len Column

Displays the length of the index used, reflecting how many bytes of the index are applied. A shorter

key_len

than the full index length indicates partial index usage.

ref Column

Shows 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 that pass table‑level filters (100 means no filtering).

Extra Column

Provides additional information such as:

Impossible WHERE : the WHERE clause is always false.

Using filesort : MySQL must sort rows using a temporary file.

Using index : a covering index satisfies the query.

Using temporary : a temporary table is created (common with GROUP BY or ORDER BY).

Using where : a WHERE filter is applied.

Using join buffer : a join buffer is used for large joins.

Index Optimization Process

Identify slow queries via the slow‑query log.

Run

EXPLAIN

to view index usage.

Focus on four columns:

key

,

key_len

,

type

, and

Extra

.

Rewrite the SQL to address any index problems discovered.

Repeat from step 2 until the execution plan shows optimal usage.

By iteratively examining these columns, developers can pinpoint missing indexes, insufficient index coverage, or inefficient join types and apply targeted optimizations.