SQL Optimization Steps and Common Scenarios

Introduction

In the early stages of application development, data volumes are small and developers focus on functional implementation; as production data grows, many SQL statements reveal performance problems that can become system bottlenecks.

General SQL Optimization Steps

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

Analyze the execution plan with EXPLAIN . Pay special attention to the type , rows , filtered and Extra columns. The type hierarchy from low to high efficiency is:

Using filesort – requires an extra pass to sort rows.

Using temporary – creates a temporary table, very slow.

Using index – uses a covering index, avoiding table row access.

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

ALL – full table scan.

index – full index scan.

range – index range scan, common for <, <=, >=, BETWEEN, IN.

ref – non‑unique index or prefix of a unique index, returns a single row.

eq_ref – uses a unique index (usually primary key) for join.

const/system – single‑row lookup, treated as constants.

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

Use SHOW PROFILE to analyze query execution.

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

Enable and examine optimizer trace.

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

Identify the problem and apply appropriate measures.

Optimize indexes.

Rewrite SQL (segment IN clauses, time ranges, incremental filtering).

Consider alternative implementations such as Elasticsearch or a data warehouse.

Handle data fragmentation.

Scenario Analysis

Case 1 – Left‑most Index Matching

Index: KEY `idx_shopid_orderno` (`shop_id`,`order_no`) SQL: select * from _t where orderno='' To use the order_no part of the composite index, the query must also include shop_id (or swap the column order in the index).

Case 2 – Implicit Type Conversion

Index: KEY `idx_mobile` (`mobile`) SQL: 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`) SQL:

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

For large offsets, either pass the last c value from the previous page (requires API change) or use a delayed‑join approach 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

MySQL evaluates IN by scanning each value, and when the IN list exceeds the eq_range_index_dive_limit (default 200), the cost model may ignore the index. Swapping the order of order_status and created_at and using delayed join can improve performance.

Case 5 – Range Query Blocking Subsequent Index Use

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

When a range condition appears on created_at, the following column order_status cannot use the index.

Case 6 – NOT / != Conditions

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 because they prevent index usage.

Case 7 – Optimizer Skipping Index for Large Result Sets

If the queried rows constitute a large portion of the table (≈20%), the optimizer may prefer a full scan via the clustered index even if a secondary index exists. select * from _order where order_status = 1; When the result set is tiny, the index will still be used.

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 aggregations consider a data warehouse; for intricate filters consider search engines like Elasticsearch.

Case 9 – Mixed ASC/DESC Order

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

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

Case 10 – Large Data Retention

When storing massive push‑notification data in MySQL with a 7‑day retention policy, frequent deletions create fragmentation; involve a DBA to perform periodic table‑level cleanup.

By following these steps and understanding each scenario, developers can systematically improve MySQL query performance.