MySQL Index and Slow Query Optimization: Concepts, Cases, and Best Practices

Introduction

Recently I helped my company resolve an online slow‑SQL alert and encountered several cases. Below I combine these cases to discuss how to optimize slow queries by using indexes.

Index Introduction

Index Concept

Indexes are ordered data structures for fast lookup, typically B‑tree (B+‑tree for clustered, secondary, covering, composite, prefix, unique indexes). Other types include hash, full‑text, and R‑Tree.

Advantages and Disadvantages

Improves data retrieval efficiency and reduces I/O.

Accelerates sorting by using index order.

Consumes storage space because an index is a separate table.

Updates (INSERT/UPDATE/DELETE) must also modify the index, adding overhead.

Index Types

Single‑column index: one column per index, multiple can exist per table.

Unique index: column values must be unique, nulls allowed.

Composite index: contains multiple columns.

Index Structures

BTree index

Hash index

Full‑text search

R‑Tree index

When to Create an Index

Primary key automatically creates a primary index.

Fields frequently used in query conditions.

Foreign key columns used for joins.

High‑concurrency scenarios benefit from composite indexes.

Sorting fields that can be accessed via an index.

Fields used in GROUP BY or aggregation.

When Not to Create an Index

Frequently updated columns.

Columns not used in WHERE clauses.

Tables with very few rows.

Tables with heavy INSERT/UPDATE/DELETE.

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

EXPLAIN Field Analysis

EXPLAIN is the most common tool for diagnosing slow SQL. mysql> EXPLAIN SELECT 1; Key fields include:

id : execution order of SELECT operations.

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

table : the table the row refers to.

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

possible_key , key , key_len , ref , rows , Extra : additional details such as Using filesort, Using temporary, Using index, Using where, etc.

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 range conditions on the right side of an index.

Prefer covering indexes to avoid SELECT *.

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

ORDER BY Optimization

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

Single‑pass sort is generally better than double‑pass.

Increase sort_buffer_size and max_length_for_sort_data to keep sorting in memory.

Case Analyses

Case 1: Too Many Parameters in IN

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

Solution: split the ID list into smaller batches (e.g., LIMIT_SIZE = 1000) and query each batch separately.

Case 2: Excessive Result Set

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

Solution: add pagination with LIMIT to reduce the amount of data returned.

Case 3: ORDER BY Slow Query

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

Problem: forcing an index on orderId while the WHERE clause does not use it leads to a full table scan (type ALL).

Case 4: JOIN Slow Query

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

Missing ON condition causes a full scan on both tables.

Case 5: Trying Different Indexes

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

Test various composite indexes (e.g., activity_id,goods_switch) to improve selectivity.

Case 6: MySQL Chooses Wrong Index

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

Force the primary key index to achieve a much lower query time.

Conclusion

Slow‑query optimization is a long‑term process that requires patience.

Final Note

If you find this article helpful, please like, share, and reply with “666” to receive a free e‑book.