Master MySQL Indexes: How Explain Optimizes Query Performance

1. Causes of Slow SQL

Hardware bottlenecks such as slow network, insufficient memory, low I/O throughput, or full disk.

Missing or ineffective indexes (e.g., after logical deletes that break the index tree).

Excessive data volume (necessitating sharding or partitioning).

Improper server configuration (my.cnf tuning).

2. Finding the Root Cause

Enable the slow‑query log and set an appropriate threshold (e.g., >3 seconds) to capture problematic statements.

Use EXPLAIN to view execution plans and SHOW PROFILE for detailed timing of each step.

Consult a DBA or operations engineer for server‑level tuning.

3. What Is an Index?

MySQL defines an index as a data structure that helps retrieve rows efficiently. In practice, the most common index is a B+Tree, which stores sorted keys and pointers to leaf pages where the actual rows reside.

The B+Tree reduces I/O dramatically: a three‑level tree can locate a row with only three disk reads, regardless of millions of rows.

4. Explain Analysis

id

The id column shows the order in which MySQL executes each step.

EXPLAIN SELECT u.*, o.* FROM user_info u, order_info o WHERE u.id = o.user_id;

select_type

SIMPLE – no UNION or subqueries.

PRIMARY – the outermost SELECT.

SUBQUERY – first SELECT inside a subquery.

UNION – second or later SELECT in a UNION.

DEPENDENT UNION – UNION that depends on outer query.

UNION RESULT – result of a UNION.

DEPENDENT SUBQUERY – subquery that depends on outer query.

DERIVED – SELECT that creates a derived table.

table

Shows which tables (or derived tables) are involved.

EXPLAIN SELECT tt.* FROM (SELECT u.* FROM user_info u, order_info o WHERE u.id = o.user_id AND u.id = 1) tt;

type

The type column indicates how MySQL accesses rows and is a key performance indicator.

system – only one row in the table (special const).

const – primary‑key or unique‑key lookup, returns at most one row.

eq_ref – one row from the joined table per row of the previous table (highly efficient).

ref – non‑unique index lookup or leftmost‑prefix of a composite index.

range – index range scan (e.g., BETWEEN, IN).

index – full index scan (no table rows read).

ALL – full table scan (slowest).

Performance ranking:

ALL < index < range ~ index_merge < ref < eq_ref < const < system

possible_keys

Indexes that MySQL could consider for the query; the actual index used is shown in the key column.

key

The index actually chosen. Example of creating a single‑column index and then observing the change:

EXPLAIN SELECT o.* FROM order_info o WHERE o.product_name='p1' AND o.productor='whh';
CREATE INDEX idx_name_productor ON order_info(productor);
DROP INDEX idx_name_productor ON order_info;

key_len

Number of bytes of the index used; helps evaluate whether a composite index is fully utilized.

ref

Shows which column(s) of the index are used for the lookup (often a constant).

rows

Estimated number of rows MySQL expects to examine; lower values indicate more efficient plans.

Before indexing, rows may be 9; after adding an appropriate index it can drop to 4 or even 1.

extra

using filesort – MySQL must sort rows after retrieval; avoid if possible.

using index – covering index scan; data found entirely in the index.

using temporary – temporary table created (often for GROUP BY or ORDER BY); may be a performance hit.

using where – rows filtered by a WHERE clause.

5. Optimization Case

Initial EXPLAIN for a LEFT JOIN shows type=ALL and no index usage:

After creating an index on the join column, type changes to ref and rows drops from 9 to 1:

Rule of thumb: for a LEFT JOIN, place the index on the right‑hand table; for a RIGHT JOIN, place it on the left‑hand table.

6. Should You Create Indexes?

Indexes dramatically speed up reads but add overhead to writes because each index is a separate B+Tree that must be maintained. They also consume additional storage.