Mastering MySQL: Proven Steps to Diagnose and Optimize Slow Queries

Preface

In early application development, data volume is small and developers focus on functionality, but as production data grows, many SQL statements reveal performance problems that can become system bottlenecks.

General Steps for SQL Optimization

1. Locate low‑efficiency SQL via slow‑query logs and similar tools.

2. Use EXPLAIN to analyze the execution plan, paying attention to type, rows, filtered, and extra.

Key type values (from low to high efficiency):

ALL – full table scan

index – full index scan

range – index range scan (used with <, <=, >=, BETWEEN, IN, etc.)

ref – non‑unique index or unique‑index prefix scan, returns a single row, common in joins

eq_ref – similar to ref but uses a unique index (primary key joins)

const/system – single row treated as a constant (primary key or unique index queries)

null – MySQL returns result without accessing any table or index

Extra information to watch:

Using filesort – MySQL performs an additional pass to sort rows.

Using temporary – a temporary table is created, indicating poor performance.

Using index – a covering index is used, avoiding table row access.

Using index condition – index condition pushdown (ICP) filters data at the storage engine level.

3. SHOW PROFILE to analyze thread states and timing.

Enable profiling with

set profiling = 1;

SHOW PROFILES;
SHOW PROFILE FOR QUERY #{id};

4. TRACE to see how the optimizer chooses a plan.

set optimizer_trace="enabled=on";
set optimizer_trace_max_mem_size=1000000;
select * from information_schema.optimizer_trace;

5. Identify the problem and apply appropriate measures.

Optimize indexes

Rewrite SQL (e.g., split IN queries, time‑range queries, filter early)

Consider alternative solutions such as Elasticsearch or data warehouses

Handle data fragmentation

Scenario Analysis

Case 1 – Left‑most matching

Index: KEY `idx_shopid_orderno` (`shop_id`,`order_no`) SQL: select * from _t where orderno='' MySQL matches columns from left to right; to use the index on order_no, the query must include shop_id or reorder the index columns.

Case 2 – Implicit conversion

Index: KEY `idx_mobile` (`mobile`) SQL: select * from _user where mobile=12345678901 Using a numeric literal on a character column forces implicit conversion, causing the index to be ignored.

Case 3 – Large pagination

Index: KEY `idx_a_b_c` (`a`,`b`,`c`) SQL:

select * from _t where a=1 and b=2 order by c desc limit 10000,10;

Solutions: pass the last c value from the previous page (e.g., c < xxx) or use a delayed join with a covering index.

select t1.* from _t t1,
(select id from _t where a=1 and b=2 order by c desc limit 10000,10) t2
where t1.id = t2.id;

Case 4 – IN + ORDER BY

Index:

KEY `idx_shopid_status_created` (`shop_id`,`order_status`,`created_at`)

SQL:

select * from _order where shop_id=1 and order_status in (1,2,3) order by created_at desc limit 10

When IN contains many values (default threshold 200), MySQL may misestimate cost, leading to suboptimal index choice. Swapping the order of order_status and created_at and using delayed join can improve performance.

Case 5 – Range query blocks subsequent index columns

Index:

KEY `idx_shopid_created_status` (`shop_id`,`created_at`,`order_status`)

SQL:

select * from _order where shop_id=1 and created_at > '2021-01-01 00:00:00' and order_status=10

Range conditions on created_at prevent the following order_status column from using the index.

Case 6 – NOT / != cannot use index (ICP may help)

select * from _order where shop_id=1 and order_status not in (1,2);
select * from _order where shop_id=1 and order_status != 1;

Avoid NOT, !=, <>, !<, !>, NOT EXISTS, NOT IN, NOT LIKE on indexed columns.

Case 7 – Optimizer skips index when many rows are accessed

If the required rows exceed roughly 20% of the table, the optimizer may prefer a full scan via the clustered index.

select * from _order where order_status = 1;

Even with an index, low‑selectivity queries may not benefit.

Case 8 – Complex queries

select sum(amt) from _t where a=1 and b in (1,2,3) and c > '2020-01-01';
select * from _t where a=1 and b in (1,2,3) and c > '2020-01-01' limit 10;

For heavy analytics, consider a data warehouse or Elasticsearch instead of raw SQL.

Case 9 – Mixed ASC/DESC order

select * from _t where a=1 order by b desc, c asc;

Mixing ASC and DESC can cause the index to be ignored.

Case 10 – Large‑scale data retention

When storing massive push‑notification data in MySQL with a 7‑day retention policy, frequent deletions cause fragmentation; coordinate with a DBA for fragment handling.

References

深入浅出MySQL：数据库开发、优化与管理维护 (Tang Hanming et al.)

MySQL技术内幕——InnoDB存储引擎 (Jiang Chengyao)

MySQL EXPLAIN Output

MySQL Cost Model

Additional Documentation