July 28, 2025

MySQL Query Optimization Case: Resolving Incorrect Index Usage

This case study explores a slow MySQL query issue caused by incorrect index selection and suboptimal execution plans. Solutions include index rebuilding, statistics updates, and query refactoring to improve performance.

Problem Phenomenon

A slow SQL query took 11 minutes to execute:

SELECT t1.HOSTID, t1.KEYATTR, t1.VALUE 
FROM xxxxxxxxxxxxx t1, yyyyyyyy t2 
WHERE t1.KEYNAME = 'cpuLoad5' 
  AND t1.CHECKTIME = '20240826' 
  AND t1.HOSTID = t2.HOSTID 
  AND t2.inspection_flag = 0;

‍

Result: 631 rows.

Faster variants (executed in 0.17s) used adjacent dates:

-- For CHECKTIME='20240825' OR '20240827'
SELECT ... FROM ... WHERE CHECKTIME IN ('20240825', '20240827');

‍

Analysis

Execution Plan Comparison
- Slow Query:
  - Full table scan on yyyyyyyy (1,049 rows).
  - Nested loops with index idx_xxxxxxxxxxxxx_hostid on t1, leading to 92,400 rows scanned.
  - Cost: 72,929.
- Fast Query:
  - Indexed scan on i_xxxxxxxxxxxxx_checktime (70,288 rows).
  - Filter KEYNAME after index lookup.
  - Cost: 48,701.
Key Issues
- Index Misuse: The optimizer preferred idx_xxxxxxxxxxxxx_hostid over i_xxxxxxxxxxxxx_checktime.
- Join Order: Joined small table t2 first, causing excessive loops (105 iterations).
- Statistics Mismatch: Optimizer underestimated rows for CHECKTIME='20240826' (expected 131,052 vs. actual 71,018).

Optimization Steps

1. Rebuild Table: Resolved fragmentation and improved index statistics.

ALTER TABLE xxxxxxxxxxxxx ENGINE=InnoDB;

Reduced execution time to 0.19s.‍

2. Forced Index: Explicitly used i_xxxxxxxxxxxxx_checktime.

SELECT /*+ FORCE INDEX(i_xxxxxxxxxxxxx_checktime) */ ...

‍

Cost: 96,269, but forced correct index usage.

3. Join Order Hints: Prioritized filtering before joining.

SELECT /*+ JOIN_ORDER(t1, t2) */ ...

Cost: 92,764, optimized join sequence.

4. Partitioning: Proposed monthly partitioning for CHECKTIME to reduce scanned data.

Lessons Learned

Index Design: Prioritize covering indexes for frequently filtered columns (CHECKTIME, KEYNAME).
Statistics Maintenance: Regularly update histograms and analyze tables to ensure accurate cost estimation.
Query Refactoring: Avoid nested loops with large datasets; filter early in the execution plariginal analysis