Master MySQL Performance: 10 Real‑World SQL Optimization Cases & Practical Steps

Overview

When an application moves from prototype to production, growing data volumes often expose inefficient SQL statements that become bottlenecks. This guide outlines a systematic method for diagnosing and fixing MySQL performance problems.

General SQL Optimization Procedure

1. Identify slow queries

Enable the slow‑query log or use Performance Schema to locate statements with high execution time.

2. Examine the execution plan with EXPLAIN

Key columns in the EXPLAIN output are type , rows , filtered and Extra . The type hierarchy from least to most efficient is:

ALL – full table scan

index – full index scan

range – index range scan (used with <, <=, >=, BETWEEN, IN, etc.)

ref – non‑unique index or prefix of a unique index, returns matching rows (common in joins)

eq_ref – unique index lookup (primary‑key join)

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

null – MySQL returns result without accessing any table

Example: with indexes idx1(a,b,c) and idx2(a,c), the query

SELECT * FROM t WHERE a=1 AND b IN (1,2) ORDER BY c;

may use idx1 (type=range) or idx2 (type=ref) depending on the optimizer’s row‑count estimate.

3. Profile the query

Enable profiling: SET profiling = 1; List profiles: SHOW PROFILES; Show details for a specific query ID:

SHOW PROFILE FOR QUERY 1;

4. Use optimizer trace

Trace why a particular plan was chosen:

SET optimizer_trace="enabled=on";

SET optimizer_trace_max_mem_size=1000000;

SELECT * FROM information_schema.optimizer_trace;

5. Apply corrective actions

Adjust indexes (add, drop, reorder columns, create covering indexes).

Rewrite SQL (split large IN lists, limit time ranges, filter early).

Consider alternative storage or processing engines (Elasticsearch, data warehouse).

Address table fragmentation (OPTIMIZE TABLE, partitioning).

Case Analyses

Case 1 – Leftmost‑prefix matching

An index is used only from its leftmost column. The index KEY idx_shopid_orderno (shop_id, order_no) cannot be used for a query that filters only on order_no. Reorder the columns to match query patterns, e.g. (order_no, shop_id) if order_no is the primary filter.

SELECT * FROM _t WHERE orderno='';

Case 2 – Implicit type conversion

Storing numeric data in a character column forces MySQL to convert the constant, which disables index usage. Ensure the column type matches the literal type, e.g.

SELECT * FROM _user WHERE mobile='12345678901';

Case 3 – Large pagination

Two practical approaches:

Use the last retrieved value as a cursor (e.g. c < ?) instead of OFFSET.

Apply a delayed join with a covering index to avoid scanning the entire offset range.

Example of delayed join:

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 evaluates an IN list by scanning n*m rows. When the list exceeds the variable eq_range_index_dive_limit (default 200), the optimizer may ignore the cost, leading to sub‑optimal plans. Swapping the order of indexed columns and using a delayed join can improve performance.

Case 5 – Range condition blocks later index columns

If a range predicate appears on the second column of a composite index, columns to the right cannot be used for index lookups. Example:

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

With index

KEY idx_shopid_created_status (shop_id, created_at, order_status)

, only shop_id and created_at are used.

Case 6 – NOT / != prevents index use

Negation operators disable index usage. Rewrite queries to avoid them or rely on index‑condition pushdown (ICP) where possible.

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

Case 7 – Optimizer skips index for large result sets

When the estimated row count exceeds roughly 20 % of the table, the optimizer may prefer a full scan via the clustered index.

SELECT * FROM _order WHERE order_status=1;

Case 8 – Complex aggregations

Heavy aggregations or multi‑condition filters are often better handled by a data warehouse or a search engine such as Elasticsearch.

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

Case 9 – Mixed ASC/DESC ordering

Mixing ascending and descending order on different columns prevents the use of a single index and forces a filesort.

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

Case 10 – Very large data volumes

Frequent deletions on large tables cause fragmentation. Periodic maintenance such as OPTIMIZE TABLE or partitioning is recommended.

Reference Materials

Deep Dive into MySQL: Development, Optimization, and Maintenance (Tang Hanming et al.)

MySQL Internals – InnoDB Storage Engine (Jiang Chengyao)

MySQL EXPLAIN Output Documentation: https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

MySQL Cost Model Documentation: https://dev.mysql.com/doc/refman/5.7/en/cost-model.html

Additional Optimization Resources: https://www.yuque.com/docs/share/3463148b-05e9-40ce-a551-ce93a53a2c66