Master MySQL SQL Optimization: Steps, Common Pitfalls & Real-World Cases

Preface

In early 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.

If you have different opinions, feel free to comment and learn together!

General Steps for SQL Optimization

1. Locate slow SQL via slow‑query log or similar tools

2. Use EXPLAIN to analyze the execution plan

Pay attention to type , rows , filtered and extra fields.

type hierarchy (from worst to best)

ALL – full table scan

index – full index scan

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

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

eq_ref – same as ref but uses a unique index (primary key)

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

null – MySQL does not read any table or index, returns result directly

Example: two indexes idx1(a,b,c) and idx2(a,c) for the query SELECT * FROM t WHERE a=1 AND b IN (1,2) ORDER BY c. If idx1 is chosen, type is range; if idx2 is chosen, type is ref. The optimizer picks the index that scans fewer rows.

Extra

Using filesort – MySQL must perform an extra pass to sort rows.

Using temporary – a temporary table is created; performance is poor.

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

Using index condition – MySQL 5.6+ ICP (index condition push‑down) filters rows at the storage engine level.

3. SHOW PROFILE analysis

Shows the status and time consumption of each thread in the query execution. Enable with

SET profiling = 1;

SHOW PROFILES;
SHOW PROFILE FOR QUERY {id};

4. TRACE

TRACE reveals how the optimizer chooses an execution 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 measures

Optimize indexes

Rewrite SQL (segment IN clauses, time ranges, filter on previous results)

Consider alternative implementations such as Elasticsearch or data warehouses

Handle data fragmentation

Scenario Analysis

Case 1 – Left‑most index matching

Index: KEY `idx_shopid_orderno` (`shop_id`,`order_no`) Query: SELECT * FROM _t WHERE orderno='' MySQL can use the index only when the leftmost column ( shop_id) is present in the WHERE clause. Swap the column order to make order_no the leading column if it is queried without shop_id.

Case 2 – Implicit type conversion

Index: KEY `idx_mobile` (`mobile`) Query: SELECT * FROM _user WHERE mobile=12345678901 Because mobile is a string column, comparing it with a numeric literal forces an implicit conversion, causing the index to be ignored.

Case 3 – Large pagination

Index: KEY `idx_a_b_c` (`a`,`b`,`c`) Query with LIMIT 10000,10 on a large offset.

Two possible optimizations: (1) use the last retrieved c value and query c < …, or (2) apply 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`)

Query uses IN on order_status and orders by created_at. MySQL searches IN values as an n*m operation, and the cost model may mis‑estimate when the IN list exceeds the eq_range_index_dive_limit (default 200).

Solution: swap the order of order_status and created_at in the index and use delayed join.

Case 5 – Range query blocks subsequent columns

Index:

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

Query filters on shop_id and a range on created_at, then equality on order_status. After a range condition, MySQL cannot use the following column for index lookup.

Case 6 – NOT / != cannot use index

Avoid using NOT, !=, <>, NOT EXISTS, NOT IN, NOT LIKE on indexed columns because they prevent index usage.

Case 7 – Optimizer prefers full scan

If the required rows constitute a large portion of the table (≈20 %), the optimizer may choose the clustered index or a full scan even when a secondary index exists.

Case 8 – Complex query

For heavy aggregations or multi‑condition queries, consider using a data warehouse or Elasticsearch instead of raw MySQL.

Case 9 – Mixed ASC/DESC order

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

Case 10 – Big data

When storing large volumes (e.g., push‑notification data) in MySQL with a limited retention period, frequent deletions cause fragmentation; involve a DBA for fragment handling.

References

深入浅出MySQL：数据库开发、优化与管理维护

MySQL技术内幕——InnoDB存储引擎

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

https://dev.mysql.com/doc/refman/5.7/en/cost-model.html

https://www.yuque.com/docs/share/3463148b-05e9-40ce-a551-ce93a53a2c66