Why Indexes Can't Save Slow SQL: Master MySQL Execution Plans with Real DBA Tips

Why Execution Plans Matter

Many developers think "if the SQL runs, it's fine; if it's slow, just add an index." In reality, the root cause is often hidden in the execution plan, and only by reading EXPLAIN can you pinpoint why an index is ignored or even makes the query slower.

How to Obtain an Execution Plan

Use EXPLAIN or DESC before a SELECT to get the estimated plan, and EXPLAIN ANALYZE (MySQL 8.0+) to run the query and collect actual runtime statistics.

mysql> EXPLAIN SELECT * FROM customers WHERE name='张三' AND age=30;

MySQL also supports different output formats: the default table format, FORMAT=JSON, and FORMAT=TREE (MySQL 8.0+).

Core Fields of the Execution Plan

id : execution order; larger id values run earlier.

select_type : query type (SIMPLE, PRIMARY, SUBQUERY, DERIVED, etc.).

type : join method – the most important performance indicator (system, const, eq_ref, ref, range, index, ALL).

possible_keys and key : indexes that could be used vs. the index actually chosen.

key_len : length of the index actually used, showing which columns participate.

rows : estimated number of rows examined.

filtered : percentage of rows that pass the remaining filters.

Extra : additional information such as Using where, Using filesort, Using index, Using temporary, etc., which often point to optimization opportunities.

Understanding Join Types (type)

From best to worst performance: system → const → eq_ref → ref → range → index → ALL. The lower the type, the fewer rows MySQL must read.

Index Usage Details

The key_len column shows how many bytes of a composite index are used. For example, a query that filters on name only uses the first part of (name, age, vip_level), while adding age=30 extends the used prefix, and adding vip_level='普通' after a range condition on age does not increase key_len because the range stops index usage.

Typical Optimization Scenarios

Left‑most prefix rule : an index can be used only if the query starts with the leftmost column of the index.

Sorting : Using filesort in Extra means MySQL cannot satisfy ORDER BY with an index; create a covering index that matches the ORDER BY columns.

Covering index : when Extra shows Using index, the query is satisfied entirely from the index without a table lookup.

Index loss cases : functions on indexed columns, implicit type conversion, and leading wildcards in LIKE prevent index usage.

Practical Examples

Sample tables ( customers, products, orders) and a series of queries demonstrate:

Valid index usage with leftmost prefix ( WHERE name='张三').

Index failure when the leftmost column is omitted ( WHERE age=30).

Sorting optimization by adding a composite index (vip_level, registration_time DESC) to eliminate Using filesort.

Covering index query ( SELECT name, age FROM customers WHERE name='张三') shows Using index.

Function and type‑conversion pitfalls ( WHERE YEAR(registration_time)=2024 vs. range on the raw column).

LIKE patterns: '%三' forces a full scan, while '张%' can use the index.

Summary and Systematic Approach

By regularly examining the execution plan you can:

Precisely locate performance bottlenecks via type , rows , and Extra .

Validate that indexes are actually used and diagnose index‑loss scenarios.

Rewrite queries or add appropriate indexes to avoid temporary tables and filesorts.

Establish a repeatable loop: Analyze → Optimize → Verify → Repeat, turning MySQL performance tuning from an art into a science.

Following this method enables you to build high‑performance, reliable database applications.

Key Takeaways

Never rely solely on adding indexes; always read the execution plan.

Understand each column of EXPLAIN to make informed decisions.

Use composite and covering indexes wisely to eliminate extra work.

Beware of functions, type conversion, and leading wildcards that break indexes.