Master MySQL Performance: Proven Steps to Optimize Slow Queries

Preface

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

SQL Optimization General Steps

Locate low‑efficiency SQL via slow‑query log

Analyze execution plan with EXPLAIN

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

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

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

Extra

Using filesort : MySQL performs an additional pass to sort rows according to the ORDER BY clause.

Using temporary : A temporary table is created to hold intermediate results; performance is poor and should be optimized.

Using index : A covering index is used, avoiding table row access; efficient unless using where also appears.

Using index condition : Since MySQL 5.6, ICP (index condition pushdown) filters data at the storage engine level, reducing row lookups.

Profile analysis with SHOW PROFILE

Enable profiling with set profiling = 1; and view results:

SHOW PROFILES;

SHOW PROFILE FOR QUERY #${id};

Trace optimizer decisions

Enable trace to see why a specific execution plan was chosen:

set optimizer_trace="enabled=on";

set optimizer_trace_max_mem_size=1000000;

select * from information_schema.optimizer_trace;

Identify problems and apply measures

Optimize indexes

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

Consider alternative implementations such as Elasticsearch or data warehouses

Handle data fragmentation

Scenario Analysis

Case 1: Left‑most index match

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

Case 2: Implicit conversion

Index: KEY `idx_mobile` (`mobile`) SQL: select * from _user where mobile=12345678901 Using a numeric literal for a character column forces an implicit conversion, causing the index to be ignored. Use a quoted string instead.

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;

Solutions: (1) Pass the last c value from the previous page and query c < ?; (2) 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 searches IN values using an n*m algorithm; cost calculation may become inaccurate when the IN list exceeds the threshold (default 200).

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;

After a range condition, columns to the right cannot use the index efficiently.

Case 6: NOT / != cannot use index

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, !=, <>, NOT EXISTS, NOT IN, NOT LIKE on indexed columns.

Case 7: Optimizer skips index for large result sets

If the queried rows exceed roughly 20% of the table, the optimizer may prefer a full table scan via the clustered index. select * from _order where order_status=1; Even with an index, low‑selectivity queries may not use it.

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 using a data warehouse; for complex business queries, alternatives like Elasticsearch may be preferable.

Case 9: Mixed ASC/DESC order

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

Mixing ASC and DESC can cause index loss.

Case 10: Large‑scale data

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