Why a Missing Index Parameter Crashed Our Production MySQL Database

During a Thursday lunch break the author was awakened by an alarm indicating that the production database server’s CPU was fully utilized and a read‑only delay alarm had been triggered for half an hour. Investigation of the SQL logs revealed a single query that scanned millions of rows, executed thousands of times with each execution taking over 3000 ms.

Root Cause: Missing Leftmost Index Field

The offending query was written by the author and omitted the user_fruit_id parameter, which is the leftmost column of a composite index

KEY `idx_userfruitid_type` (`user_fruit_id`,`task_type`,`receive_start_time`,`receive_end_time`) USING BTREE

. Because the leftmost field was not supplied, MySQL could not use the index and performed a full‑table scan.

To restore service a temporary fix was applied by adding a new composite index that excluded user_fruit_id:

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

After the new index was created, the scan row count dropped dramatically and the instance returned to normal operation.

Understanding the Leftmost Matching Principle

MySQL composite indexes are built on a B+‑tree using the leftmost column as the tree key. An index can be used only if the query predicates include the leftmost column; subsequent columns are usable only when the preceding ones are constrained. For example, with an index on (name, age), a condition on name alone or on both name and age can use the index, but a condition on age alone cannot.

Bug Identification in Application Code

The Java request class GardenUserTaskListReq correctly annotated userFruitId with @NotNull, but the controller method lacked the @Validated annotation, so the validation never executed:

public class GardenUserTaskListReq implements Serializable {
    private static final long serialVersionUID = -9161295541482297498L;
    @ApiModelProperty(notes = "水果id")
    @NotNull(message = "水果id不能为空")
    private Long userFruitId;
    // ...
}

Furthermore, the code did not verify whether the referenced user_fruit_id existed in the related table, allowing any value to trigger the costly query.

Key Takeaways

Never trust external callers; validate all critical parameters, even non‑null values, and reject invalid input before constructing SQL.

Run EXPLAIN on queries that operate on large tables and ensure appropriate indexes are in place.

Perform thorough code reviews; missing annotations or validation logic can introduce subtle bugs that cause severe production incidents.

By addressing the missing index field, adding proper validation, and reviewing the code, the team prevented future occurrences of similar outages.