MySQL CPU Saturation Case Study: Analyzing and Optimizing a Slow ORDER BY Query

Background

On 2019‑01‑11 at 9:00 AM a MySQL database on a server with 256 GB RAM and 48 CPU cores reached 100% CPU usage.

Analysis Process

Monitoring (PMM) showed the CPU spike coincided with a large number of slow queries. The most frequent entry in the slow‑log was extracted with: mysqldumpslow -s c slow.log > /tmp/slow_report.txt The top slow query scanned 1,161,559 rows (Rows_examined) indicating a full‑table (actually full‑index) scan.

Further investigation revealed the query:

SELECT T.TASK_ID, T.xx, ... FROM T_xxx_TASK T WHERE N=N AND T.STATUS IN (N,N,N) AND IFNULL(T.MAX_OPEN_TIMES,N) > IFNULL(T.OPEN_TIMES,N) AND (T.CLOSE_DATE IS NULL OR T.CLOSE_DATE >= SUBDATE(NOW(),INTERVAL 'S' MINUTE)) AND T.REL_DEVTYPE = N AND T.REL_DEVID = N AND T.TASK_DATE >= 'S' AND T.TASK_DATE <= 'S' ORDER BY TASK_ID DESC LIMIT N,N

The execution plan showed type: index (full‑index scan) and Rows: 644, which is only an estimate; the actual rows examined were over one million.

Key Findings

The index used (INDX_BIOM_ELOCK_TASK3) avoided a file sort but still required scanning many rows because the REL_DEVID column has high cardinality while TASK_DATE has low selectivity.

Using force index on the low‑selectivity index made the query faster, confirming that the optimizer’s cost‑based choice did not match real performance.

The Extra: Using where indicated a need for a covering index to avoid row look‑ups.

Optimization Recommendations

Add a composite index that matches the query predicates and the ORDER BY clause:

ALTER TABLE T_BIOMA_ELOCK_TASK ADD INDEX idx_REL_DEVID_TASK_ID (REL_DEVID, TASK_ID);

Also drop the low‑selectivity TASK_DATE index.

Note the implicit conversion issue: REL_DEVID is a VARCHAR, so the value must be quoted in the SQL (e.g., AND T.REL_DEVID = '000000025xxx').

Result

After adding the new index, the query execution time dropped from >10 seconds to 0.00 sec (milliseconds), confirming the effectiveness of the index redesign.

Conclusion

This typical ORDER BY query optimization demonstrates that an index appearing in the execution plan does not guarantee fast execution; the index must be well‑aligned with the query’s filter conditions and ordering requirements.