Root Cause Analysis and Optimization of a Slow MySQL Query Using Index Selection and Force Index

During a routine operation a monitoring alert reported a MySQL query that took 60 seconds to complete. The original statement was a simple pagination query: select * from xxx where gear_id=xxx and examine=xxx order by id desc limit 10 .

The query performed quickly when examine=2 but became extremely slow when examine=3 . The execution plan showed that although the index idx_gear_id was listed in possible_keys , MySQL chose the PRIMARY (the clustered index) as the actual key and applied the WHERE filter after scanning the primary B+‑tree.

This behavior occurs because InnoDB stores the table rows in the primary clustered index. When the optimizer cannot use the secondary index efficiently, it falls back to a full scan of the primary index and filters rows with the WHERE clause, which is costly for large tables.

InnoDB distinguishes two index types:

Clustered index : a B+‑tree built on the primary key where leaf nodes contain the full row data.

Non‑clustered (secondary) index : a B+‑tree that stores the indexed columns plus the primary key value; retrieving the full row requires a second lookup (the “back‑table” operation).

Because a secondary index lookup would return many primary‑key values that still need to be sorted and limited, MySQL estimates a higher cost than scanning the primary index directly, especially when the ORDER BY id DESC LIMIT 10 clause forces a sort.

To force the use of the appropriate secondary index, the query can be rewritten with FORCE INDEX :

select * from xxx force index (idx_gear_id) where gear_id=xxx and examine=3 order by id desc limit 10

While this immediate fix restores acceptable performance, a long‑term strategy is recommended. As data volume grows and query conditions become more complex (e.g., JSON field filters), the team plans to offload the searchable fields to Elasticsearch. The synchronization pipeline uses JD Tech’s DTS middleware to capture MySQL binlog events, extract indexed columns and the business‑unique ID, and write them into an ES index.

In the new workflow, user‑facing queries first hit Elasticsearch; the matching IDs are then used to fetch the detailed rows from MySQL, reducing load on the relational database and improving response time for complex search scenarios.