Optimizing a Date Range Query

I ran into this while retrieving events from two short time windows in a large MySQL table.

The original query was straightforward:

SELECT EventID, ChannelID, `OccurredAt`
FROM ledger.events
WHERE (`OccurredAt` BETWEEN '2026-09-25 20:03:00'
                        AND '2026-09-25 20:27:00')
   OR (`OccurredAt` BETWEEN '2026-09-12 22:19:00'
                        AND '2026-09-12 22:41:00')
ORDER BY `OccurredAt`;

The time windows were narrow, but EXPLAIN showed a full table scan:

The table had no index beginning with OccurredAt, and adding one was not an option. It did have this composite index:

KEY `idx_flow_state_time` (`FlowType`, `StateCode`, `OccurredAt`)

Making the existing index usable

The original query filtered only on OccurredAt, the third column in the index. I checked which combinations of the first two columns existed:

SELECT FlowType, StateCode
FROM ledger.events FORCE INDEX (`idx_flow_state_time`)
GROUP BY FlowType, StateCode;

EXPLAIN showed Using index for group-by. The lookup returned 27 existing combinations across two flow types.

I added those combinations to the date-range query:

SELECT EventID, ChannelID, `OccurredAt`
FROM ledger.events FORCE INDEX (`idx_flow_state_time`)
WHERE (
    (FlowType = 'Outbound'
     AND StateCode IN (0,1,6,20,21,22,31,49,50,51,97,98,99,100))
 OR (FlowType = 'Inbound'
     AND StateCode IN (0,1,6,20,21,22,30,49,50,51,98,99,100))
)
AND (
    `OccurredAt` BETWEEN '2026-09-25 20:03:00'
                     AND '2026-09-25 20:27:00'
 OR `OccurredAt` BETWEEN '2026-09-12 22:19:00'
                     AND '2026-09-12 22:41:00'
)
ORDER BY `OccurredAt`;

This supplied values for the leading index columns, followed by ranges on the timestamp. I checked EXPLAIN before running the query, since FORCE INDEX is only useful when the resulting plan is sound.

The estimate dropped from roughly 488 million rows to 33,603, and the query returned quickly on a read replica. These figures are optimizer estimates, not measured row counts or execution times.

Using filesort remained: rows from different flow and state groups still had to be sorted together by OccurredAt. The improvement came from reading narrower index ranges.

The trade-off

The state lists covered the combinations present when I inspected the table. A new state or flow type could make this version of the query incomplete. For a recurring report, those values would need to be maintained or validated.

For this one-time request, the rewrite used an existing index without a schema change. It was a useful reminder to inspect the order of columns in a composite index: a date range can become accessible when the query can safely supply values for the columns before it.