Mastering MySQL: Proven Steps to Optimize Slow Queries

SQL Optimization General Steps

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

2. Use EXPLAIN to analyze the execution plan

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

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 (primary‑key or unique index query)

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

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 rows at the storage engine level

3. SHOW PROFILE to analyze query execution timing (enable with set profiling = 1; )

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

4. TRACE to see how the optimizer chooses execution plans

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

5. Identify problems and apply appropriate measures

Optimize indexes

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

Consider alternative storage (e.g., Elasticsearch, data warehouse)

Handle data fragmentation

Scenario Analyses

Case 1 – Left‑most index matching

Index: KEY `idx_shopid_orderno` (`shop_id`,`order_no`) Query must include shop_id before order_no to use the index efficiently.

Case 2 – Implicit conversion

Index: KEY `idx_mobile` (`mobile`) Query uses a numeric literal for a VARCHAR column, causing implicit conversion and index loss.

Case 3 – Large pagination

Typical solution: use the last retrieved c value as a cursor (e.g., c < xxx) or 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

MySQL searches IN values using an n*m approach; large IN lists (>200) may cause inaccurate cost estimation.

Case 5 – Range query blocks subsequent index usage

When a range condition appears before other indexed columns, later columns cannot use the index.

Case 6 – NOT / != conditions prevent index usage

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

Case 7 – Optimizer skips index for large result sets

If a query touches a substantial portion of the table (≈20%), the optimizer may prefer a full scan via the clustered index.

Case 8 – Complex queries

Consider using a data warehouse or Elasticsearch for heavy aggregations instead of complex SQL.

Case 9 – Mixed ASC/DESC ordering

Mixing ASC and DESC in ORDER BY can invalidate index usage.

Case 10 – Big data retention

Frequent data cleanup in large MySQL tables can cause fragmentation; involve a DBA for fragment handling.