SQL Optimization Steps and Common Cases for Improving Query Performance

In the early stage of 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.

SQL Optimization General Steps

Locate slow queries through slow‑query logs or similar tools.

Analyze execution plans using EXPLAIN and pay attention to type, rows, filtered, and extra columns.

EXPLAIN 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 scan or unique‑index prefix scan, returns a single row (common in joins)

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

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

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

When two indexes exist (e.g., idx1(a,b,c) and idx2(a,c)) the optimizer chooses based on the estimated cost; if idx2 scans five times more rows than idx1, idx1 is preferred.

EXTRA information

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

Using temporary – a temporary table is created for intermediate results, which is costly.

Using index – a covering index is used, avoiding table row access; if Using where also appears, the index cannot fully satisfy the query.

Using index condition – MySQL 5.6+ pushes predicates to the storage engine (ICP), reducing row look‑ups.

SHOW PROFILE analysis

Enable profiling with SET profiling = 1; and view details:

SHOW PROFILES;

SHOW PROFILE FOR QUERY #{id};

TRACE analysis

Enable optimizer trace to see why a plan was chosen:

set optimizer_trace="enabled=on";

set optimizer_trace_max_mem_size=1000000;

select * from information_schema.optimizer_trace;

Problem identification and measures

Optimize indexes

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

Consider alternative implementations (Elasticsearch, data warehouse)

Handle data fragmentation

Scenario Analysis – Real Cases

Case 1: Left‑most index matching

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

Case 2: Implicit conversion

Index: KEY `idx_mobile` (`mobile`) SQL: select * from _user where mobile=12345678901 Numeric literal forces implicit conversion, causing index loss; use a string literal instead.

Case 3: Large pagination

SQL:

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

Solutions: pass the last c value (c < …) or use delayed join with a covering index.

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 calculates IN cost as n*m; when the IN list exceeds the eq_range_index_dive_limit (default 200), the optimizer may choose a sub‑optimal index.

Case 5: Range query blocks subsequent index usage

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 condition on created_at prevents using the trailing order_status index.

Case 6: NOT / != conditions

SQL examples:

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, !=, <> etc. on indexed columns.

Case 7: Optimizer skips index for large result sets

When the expected row count exceeds ~20% of the table, the optimizer may prefer a full table scan even if an index exists.

Case 8: Complex queries

Examples of aggregation and multi‑condition queries that may be better served by a data warehouse or Elasticsearch.

Case 9: Mixed ASC/DESC ordering

SQL: select * from _t where a=1 order by b desc, c asc Mixed ordering can cause index loss.

Case 10: Large‑scale data storage

For push‑notification data stored in MySQL with a 7‑day retention, frequent deletions cause fragmentation; DBA intervention is required.

Overall, understanding EXPLAIN output, profiling, and trace information, combined with proper index design and query rewriting, is essential for maintaining MySQL performance.