Why MySQL Sometimes Skips Your Index on Range Queries (and How to Fix It)

Indexes dramatically speed up database queries, especially when you create them on frequently filtered columns such as trans_date. An equality query like SELECT * FROM A WHERE trans_date = '20220222' will reliably use the union_idx_query index.

However, when the same column is used in a range condition (e.g., WHERE trans_date > '20220122'), MySQL does not always use the index. An EXPLAIN of that query shows a full‑table scan, indicating that the optimizer decided the index would not be efficient.

Changing the constant value can alter the optimizer's decision. For example, SELECT * FROM t_trans_log_info WHERE trans_date > '20220222' results in an EXPLAIN that shows the index is used. The difference stems from the optimizer estimating how many rows the range will return.

The optimizer prefers a full scan when the estimated rows accessed via the index exceed roughly 10‑30% of the total table size, because scanning the index and then fetching rows would be slower than reading the table sequentially. This threshold varies with data distribution and query context, and even forcing an index may be ignored if the optimizer deems it inefficient.

Range queries ( >, <, >=, <=, IN, etc.) often trigger this behavior. To ensure the index remains effective, you may need to add additional limiting conditions, rewrite the query, or create more selective composite indexes.