Master MySQL Slow Query Optimization: Index Principles and Real‑World Cases

Why a Slow Query Triggers Index Analysis

A feature became increasingly slow. The initial reaction was to add an index on every column, which is ineffective. Understanding the query pattern leads to a composite (multi‑column) index that follows the left‑most prefix rule.

MySQL Index Fundamentals

Purpose of an Index

An index works like a dictionary: it narrows the search space so the engine can locate rows without scanning the whole table.

Disk I/O and Prefetch

Typical mechanical disks need about 9 ms per I/O (seek + rotational latency + transfer). One I/O can execute hundreds of thousands of CPU instructions, so minimizing I/O is crucial for performance.

Data Structure: B+‑Tree

MySQL stores indexes in a multi‑way search tree, most commonly a B+‑tree. Each disk page (4 KB or 8 KB) holds many keys and child pointers, keeping the tree height low even for millions of rows.

B+‑Tree Search Process

To locate key 29, the engine loads the root page, performs a binary search to choose the correct child pointer, loads the next page, and repeats until the leaf page is reached. In a three‑level tree only three I/Os are required, compared with a full table scan that would need one I/O per row.

Key Properties

Tree height h = log_{m+1}(N), where m is the number of keys per page and N the row count. Smaller key types (e.g., INT vs BIGINT) increase m and reduce height.

Composite indexes obey the left‑most prefix rule: the index can be used only for the leading columns until a range condition ( >, <, BETWEEN, LIKE) appears. Missing the first column makes the whole index unusable.

Principles for Building Indexes

Follow the left‑most prefix rule; stop at the first range condition.

Equality ( =) and IN predicates can appear in any order.

Prefer columns with high cardinality (distinct/total > 0.1 for join columns).

Avoid functions on indexed columns; rewrite expressions so the column appears raw.

Extend existing indexes when possible instead of creating new ones.

Using EXPLAIN to Diagnose

The rows column in EXPLAIN output is the primary metric—reducing it usually yields faster execution.

Basic Steps for Slow Query Optimization

Confirm the query is truly slow (disable query cache with SQL_NO_CACHE).

Identify the table that returns the fewest rows and start filtering there.

Run EXPLAIN and verify the plan matches expectations.

Prioritize ORDER BY … LIMIT queries for early filtering.

Understand the business usage scenario.

Apply the indexing principles when adding or modifying indexes.

Iterate: observe the effect, adjust the plan, and repeat.

Case Studies

Complex Query Rewrite

Original query took 1.87 s for 53 rows. By splitting the join and using UNION (instead of UNION ALL when DISTINCT is required), execution time dropped to 10 ms—a 200‑fold improvement.

Column Selectivity

A table with low‑selectivity columns ( accurate_result, sync_status) caused a full‑table scan of millions of rows. After confirming the actual usage pattern, a targeted composite index reduced runtime from 6.22 s to 200 ms.

When Optimization Is Hard

A query sorted a large intermediate result (≈ 780 k rows) before joining, leading to severe slowdown. Reordering to join first, then sort, and using STRAIGHT_JOIN cut execution time from > 2 min to < 1 ms. However, certain nested‑loop patterns remain inherently difficult to optimize.

These examples demonstrate that adding indexes alone does not guarantee improvement; a deep understanding of execution plans, data distribution, and business logic is essential.