Mastering MySQL Performance: Practical Strategies for Slow‑SQL Optimization

In MySQL‑InnoDB, SQL performance degrades as data volume grows, leading to resource contention and possible system redesign. Optimizing slow queries is essential for scalability.

Key Factors Affecting Execution

Data volume : Larger result sets increase I/O and can become a bottleneck.

Data access method : Cache vs. disk, ability to use a global index, and predicate selectivity affect scan cost.

Data processing method : Sorting, sub‑queries, aggregation, and joins often require temporary tables and consume CPU.

Optimization Strategies

Reduce data scans : Add selective predicates (e.g., time filters) and ensure indexes are used to avoid full‑table scans.

Return less data : Limit columns and rows to reduce network and disk I/O.

Reduce interaction rounds : Cache frequently accessed small result sets in application memory or Redis.

Lower CPU and memory overhead : Optimize join order, avoid large transactions, and add resources only when necessary.

Practical Cases

1. Pagination

Traditional LIMIT offset, count scans many rows for large offsets. Two alternatives:

SELECT * FROM table_demo WHERE type = ? LIMIT ?,?;

Method 1 – Offset by ID

lastId = 0; -- or MIN(id)
DO {
  SELECT * FROM table_demo WHERE type = ? AND id > lastId LIMIT ?;
  lastId = MAX(id);
} WHILE (result NOT EMPTY);

Method 2 – Segment query (parallelizable, requires contiguous IDs)

minId = MIN(id);
maxId = MAX(id);
FOR (i = minId; i <= maxId; i += pageSize) {
  SELECT * FROM table_demo WHERE type = ? AND id BETWEEN i AND i+pageSize;
}

2. GROUP BY

Filter rows before grouping to avoid unnecessary processing:

SELECT job, AVG(sal) FROM table_demo WHERE job = 'manager' GROUP BY job;

3. Range Query & Index Choice

When a composite index contains a range column, only the leftmost columns can be used for index lookups. For queries filtering by trade_date_time range and then by org_code, the index (trade_date_time, org_code) is more effective than (org_code, trade_date_time).

4. ORDER BY

Define covering indexes that match the ORDER BY columns. Example of a slow query that timed out versus an optimized version that finishes in 34 ms:

-- Slow (timeout)
SELECT id, ..., create_time FROM statement
WHERE account_number='XXX' AND create_time BETWEEN '2022-04-24 06:03:44' AND '2022-04-24 08:03:44' AND dc_flag='C'
ORDER BY trade_date_time DESC, id DESC
LIMIT 0,1000;

-- Optimized (34 ms)
SELECT id, ..., create_time FROM statement
WHERE account_number='XXX' AND create_time BETWEEN '2022-04-24 06:03:44' AND '2022-04-24 08:03:44' AND dc_flag='C'
ORDER BY create_time DESC, id DESC
LIMIT 0,1000;

5. Large‑Table Strategies

Horizontal/vertical sharding (分库分表)

Read‑write separation

Periodic data archiving

Index Design Principles

Build indexes on columns with unique or highly selective values, frequent WHERE conditions, GROUP BY/ORDER BY usage, and join keys.

Avoid indexes on low‑cardinality columns, very small tables (< 1 000 rows), or columns that are frequently updated.

Prefer composite indexes with the most selective column first.

Use prefix indexes for long string columns to save space and speed comparisons.

Advanced Index Features

Multi‑Range Read (MRR)

MySQL 5.6+ can sort primary‑key IDs from a secondary index and fetch rows in order, reducing random I/O.

Index Push‑Down

From MySQL 5.6 onward, secondary indexes can filter rows (e.g., age > 18) before the engine performs the table lookup, decreasing the number of row fetches.