Understanding MySQL Slow Query Analysis with EXPLAIN

When an interface response is slow, the most common cause is inefficient SQL queries; developers need to diagnose the problem by examining how MySQL processes a query.

The article first introduces MySQL’s architecture, describing the client connection, business logic modules, connection management, query cache, parser, optimizer, executor, storage engine, and physical files.

It then shows how the EXPLAIN keyword can simulate the optimizer’s execution plan, revealing details such as id , select_type , table , partitions , type , possible_keys , key , key_len , ref , rows , filtered , and Extra . Among these, type , key , and Extra are highlighted as the most important for performance analysis.

The type column indicates join types from best to worst performance: const → eq_ref → ref → range → index → all. Each type is explained with examples and visual illustrations.

The key column shows which index (if any) the optimizer actually used; a NULL value means no index was applied.

The Extra column may contain messages such as Using index (covering index, good performance), Using filesort (external sorting, usually undesirable), and Using temporary (temporary table creation, often a sign of inefficient ORDER BY or GROUP BY).

Finally, the article advises developers to focus on these fields to determine whether a query needs optimization and suggests enabling MySQL’s slow query log for further investigation.