Why Does MySQL Ignore an Index Until the Date Range Is Narrowed?

In the users table an index exists on the create_time column.

When running the following query over a wide date range, the EXPLAIN output shows that no index is used:

SELECT * FROM users
WHERE create_time BETWEEN '2014-08-01 00:00:00' AND '2015-08-30 00:00:00';

The possible_keys column lists the index, but the key column is empty and type is ALL, indicating a full‑table scan.

After narrowing the start date to '2015-08-01' and re‑executing, the EXPLAIN result shows that the index is now chosen:

The optimizer first identifies candidate indexes (the possible_keys list). If multiple indexes are applicable, it selects the one with the lowest estimated cost.

It also evaluates whether using the chosen index would require scanning more than about 30 % of the table rows. If the estimated row coverage exceeds this threshold, the optimizer may prefer a full table scan, leaving the key column empty.

Other factors such as table size, row count, and I/O block size further influence the decision.