Debugging a High‑Load SQL Query: Index Miss and Temporary Fix

During a routine check, the team discovered that the online database server's CPU was fully utilized, triggering a read‑only alert for half an hour. The root cause was a single SQL statement that performed a full‑table scan, scanning millions of rows and executing over ten thousand times with each execution taking more than 3000 ms.

The problematic query lacked the leftmost column user_fruit_id required by the composite index idx_userfruitid_type(user_fruit_id, task_type, receive_start_time, receive_end_time) , so MySQL could not use the index and fell back to a full scan.

To mitigate the immediate impact, a new composite index was added that omitted user_fruit_id :

KEY `idx_task_type_receive_start_time` (task_type, receive_start_time, receive_end_time, created_time) USING BTREE

After applying the new index and restarting the instance, the scan row count dropped dramatically and the service returned to normal.

The post then explains MySQL's "left‑most matching principle" for composite indexes, illustrating with a two‑column (name, age) example: the index can be used when the leftmost column is present in the WHERE clause, but not when only the second column is queried.

Further investigation revealed a bug in the Java code: the request object GardenUserTaskListReq annotated @NotNull on userFruitId but the controller method lacked the @Validated annotation, so the non‑null check never executed. Additionally, there was no existence check for the referenced user_fruit_id in the related table, causing the query to run even with invalid parameters.

Key lessons include:

Never trust external callers; always validate critical parameters.

Run EXPLAIN on high‑volume queries and ensure appropriate indexes are in place.

Conduct thorough code reviews to catch simple validation oversights.

These practices help prevent minor bugs from escalating into system‑wide outages.