Master MySQL Slow Query Optimization: Indexes, EXPLAIN, and Real-World Cases

Preface

Recently I helped my company resolve an online slow‑SQL alert and encountered several cases. This article analyzes those slow‑query cases and discusses how to optimize them.

Index Overview

Index Concept

An index is a sorted data structure for fast lookup. By default MySQL uses B‑tree (B+‑tree) indexes such as primary, secondary, covering, composite, prefix, and unique indexes; other types include hash, full‑text, and R‑Tree.

Advantages and Disadvantages

Advantages:

Improves data retrieval efficiency and reduces I/O.

Enables sorting via the index, lowering CPU cost.

Disadvantages:

Indexes occupy additional storage.

Insert/Update/Delete operations must also maintain index pages.

Index Types

Single‑column index

Unique index

Composite index

Index Structures

B‑Tree

Hash

Full‑text

R‑Tree

When to Create an Index

Primary key automatically creates a primary index.

Columns frequently used in query predicates.

Foreign‑key columns used for joins.

High‑concurrency scenarios benefit from composite indexes.

Columns used for ORDER BY or GROUP BY.

Columns involved in aggregation.

When Not to Create an Index

Columns that are updated frequently.

Columns not used in WHERE conditions.

Tables with very few rows.

Tables with heavy INSERT/UPDATE/Delete activity.

Low‑selectivity columns (e.g., boolean flags).

EXPLAIN Field Analysis

EXPLAIN is the most common tool for diagnosing slow SQL.

mysql> EXPLAIN SELECT 1;

Key fields:

id : execution order of SELECT operations.

select_type : SIMPLE, PRIMARY, SUBQUERY, DERIVED, UNION, UNION RESULT.

table : table name.

type : access type (system, const, eq_ref, ref, range, index, all).

possible_key : indexes that could be used.

key : index actually used.

key_len : length of the index used.

ref : columns or constants used with the index.

rows : estimated rows examined.

Extra : additional information such as Using filesort, Using temporary, Using index, Using where, Using join buffer, impossible where, select table optimized away.

Index Invalidations

Prefer full value matches.

Follow the left‑most prefix rule for composite indexes.

Avoid functions or type conversions on indexed columns.

Storage engines cannot use columns to the right of a range condition.

Use covering indexes to avoid SELECT *.

Operators like !=, <>, IS NULL, IS NOT NULL, and leading wildcards in LIKE prevent index usage.

Minimize use of OR.

ORDER BY Optimization

Avoid filesort by sorting on an index that follows the left‑most prefix rule.

Two sorting methods: double‑pass (two disk scans) and single‑pass (in‑memory). Single‑pass is generally better.

Increase sort_buffer_size and max_length_for_sort_data to allow more data to be sorted in memory.

Case Analyses

Case 1 – Too Many Parameters in IN

select * from goods_info where goods_status = ? and id in (11,22,33,...)

Large IN lists increase result size and cause slow queries. Solution: split the list into smaller batches (e.g., 1000 IDs per batch) and query each batch separately.

Case 2 – Excessive Result Set

select * from goods where goods_status = ? and poi_id = ?

Convert to paginated queries, e.g., adding goods_id > 22 ORDER BY goods_id LIMIT 2000, to reduce data volume per request.

Case 3 – ORDER BY Slow Query

SELECT * FROM order FORCE INDEX(orderid) WHERE orderId = 11 AND status IN (0,22) ORDER BY id ASC;

Forcing an index that is not used in the WHERE clause leads to a full table scan (type: ALL). Removing the unnecessary FORCE INDEX resolves the issue.

Case 4 – JOIN Without ON Condition

select * from useract join userinfo order by useracct.id desc limit 11;

Missing ON clause causes a Cartesian product and full table scans. Adding the proper join condition and appropriate indexes fixes the performance.

Case 5 – Trying Different Indexes

select id from goods_info where id > ? and activity_id = ? and goods_switch in (?) limit ?

Testing showed low hit rate for the activity_id index; adding a composite index on (activity_id, goods_switch) dramatically improved the query.

Case 6 – MySQL Chooses the Wrong Index

select * from goods_info where goods_source = ? and goods_switch != ? and id > ? order by id limit ?

The optimizer selected the single‑column index on goods_source instead of the primary key. Adding a hint to force the primary key reduced execution time.

Conclusion

Slow‑query optimization is a long‑term process that requires patience and systematic testing of indexes, query rewrites, and execution plans.