SQL Optimization Steps and Common Scenarios

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

SQL optimization general steps

1. Locate low‑efficiency SQL via slow‑query logs.

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

3. Use SHOW PROFILES and SHOW PROFILE FOR QUERY #{id} to see thread status and time consumption (enable with SET profiling = 1; ).

4. Enable optimizer trace to understand why a plan was chosen:

SET optimizer_trace="enabled=on";

SET optimizer_trace_max_mem_size=1000000;

SELECT * FROM information_schema.optimizer_trace;

5. Determine the problem and apply appropriate measures.

EXPLAIN type values

ALL – full table scan

index – full index scan

range – index range scan (e.g., <, <=, >=, BETWEEN, IN)

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

eq_ref – unique index lookup (primary key join)

const/system – single‑row result, treated as constant

null – no table or index accessed, returns result directly

EXPLAIN Extra information

Using filesort – MySQL needs 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 – index condition pushdown (ICP) filters data at the storage engine.

Scenario analysis

1. Leftmost index matching : the query must include the leftmost indexed column (e.g., shop_id before order_no ).

KEY `idx_shopid_orderno` (`shop_id`,`order_no`)

SELECT * FROM _t WHERE orderno='';

2. Implicit conversion : comparing a string column with a numeric literal disables the index.

KEY `idx_mobile` (`mobile`)

SELECT * FROM _user WHERE mobile=12345678901;

3. Large pagination : avoid deep offsets; use “last‑row” pagination or delayed join.

SELECT * 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;

4. IN + ORDER BY : MySQL processes IN as n*m lookups; large IN lists (>200) may cause inaccurate cost estimation.

SELECT * FROM _order WHERE shop_id=1 AND order_status IN (1,2,3) ORDER BY created_at DESC LIMIT 10;

5. Range query blocking : after a range condition, subsequent columns cannot use the index.

SELECT * FROM _order WHERE shop_id=1 AND created_at > '2021-01-01 00:00:00' AND order_status=10;

6. Not‑equal / NOT IN : these predicates prevent index usage.

SELECT * FROM _order WHERE shop_id=1 AND order_status NOT IN (1,2);

7. Optimizer skipping index : when a large portion of the table is accessed, the optimizer may prefer a full scan.

SELECT * FROM _order WHERE order_status = 1;

8. Complex queries : consider data‑warehouse or search engine solutions for heavy aggregations.

SELECT SUM(amt) FROM _t WHERE a=1 AND b IN (1,2,3) AND c > '2020-01-01';

9. Mixed ASC/DESC : mixing sort directions can invalidate index usage.

SELECT * FROM _t WHERE a=1 ORDER BY b DESC, c ASC;

10. Big data storage : frequent data cleanup creates fragmentation; involve DBA for maintenance.