Why Does EXPLAIN Show Using filesort Even When a Composite Index Covers WHERE and ORDER BY?

In an online order‑query interface, a SELECT with WHERE status = 1 AND channel = 3 and ORDER BY created_at DESC becomes slower as data grows, and EXPLAIN shows Using filesort even though the composite index idx_status_channel_created (status, channel, created_at) seems to cover both the filter and the sort.

What Using filesort Actually Means

The name is misleading; it indicates that MySQL cannot use the index’s inherent order to produce the final result and must perform an additional sort in memory (or on disk if sort_buffer_size is insufficient). Any occurrence of filesort adds extra work, which can become costly on large datasets.

How Composite Index Ordering Works

For an index defined as (a, b, c), the B+‑tree stores rows ordered first by a, then b within equal a, and finally c within equal a and b – the classic left‑most‑prefix ordering.

Index (a, b, c) leaf order:
(1, 1, 10)
(1, 1, 20)
(1, 2, 5)
(1, 2, 15)
(2, 1, 3)
(2, 1, 8)
(2, 3, 1)
(2, 3, 12)

When a = 1, b is ordered as 1,1,2,2.

When a = 1 AND b = 1, c is ordered as 10,20.

If a preceding condition is a range (e.g., a = 1 but b varies), the ordering of later columns ( c) is no longer guaranteed across the whole result set.

Root Cause: Range Queries Break Order Propagation

Changing the query to use channel > 2 turns the channel predicate into a range. The index can locate rows where status = 1 AND channel > 2, but created_at is only ordered within each individual channel value, not globally. Sample index rows illustrate the jump in created_at when moving from channel = 3 to channel = 5:

(1, 3, 2026-07-01 08:00:00)  -- channel=3, created_at ordered
(1, 3, 2026-07-05 10:30:00)
(1, 3, 2026-07-10 14:00:00)
(1, 5, 2026-07-02 09:00:00)  -- channel=5, created_at jumps back
(1, 5, 2026-07-08 16:00:00)
(1, 7, 2026-07-03 11:00:00)  -- channel=7, another jump
(1, 7, 2026-07-15 20:00:00)

Because the global order of created_at is broken, MySQL must read the matching rows and then perform an extra sort, which appears as Using filesort in the EXPLAIN output.

ORDER BY Field Order Mismatch

Even with all equality predicates, if the ORDER BY clause lists columns in a different order than the index definition, the optimizer cannot use the index for sorting. Example:

SELECT * FROM t_order
WHERE status = 1
ORDER BY created_at, channel;

The index stores rows as (status, channel, created_at), i.e., channel precedes created_at. Requesting the opposite order forces a filesort.

ASC/DESC Mixing Before MySQL 8.0

Prior to MySQL 8.0, a B+‑tree index could only support a single sort direction. A query such as ORDER BY channel ASC, created_at DESC therefore required a filesort. MySQL 8.0 introduced descending indexes, allowing mixed directions:

CREATE INDEX idx_status_channel_created
ON t_order (status ASC, channel ASC, created_at DESC);

With this index, the query

WHERE status = 1 AND channel = 3 ORDER BY channel ASC, created_at DESC

can use index ordering directly. Older versions cannot.

Verification and Diagnosis

Run EXPLAIN and inspect the Extra column. Presence of Using filesort means the sort is not covered by the index; absence (e.g., only Using index condition or Using where) indicates the index is handling the ordering.

EXPLAIN SELECT * FROM t_order
WHERE status = 1 AND channel > 2
ORDER BY created_at DESC LIMIT 20;

id: 1
select_type: SIMPLE
type: range
key: idx_status_channel_created
rows: 12890
Extra: Using index condition; Using filesort

Optimization Strategies

Option 1: Reorder Index Columns

If the filtering on channel is low‑selectivity, place the sorting column first:

ALTER TABLE t_order
ADD INDEX idx_status_created_channel (status, created_at, channel);

This lets WHERE status = 1 ORDER BY created_at DESC use the index for sorting, but the channel filter must be applied after the index scan, which may be acceptable depending on its selectivity.

Option 2: Convert Range to Equality (IN)

If the range contains a limited set of values, replace it with an IN list:

SELECT * FROM t_order
WHERE status = 1 AND channel IN (3,5,7)
ORDER BY created_at DESC LIMIT 20;

The optimizer treats each channel = X as an equality condition, allowing index ordering for each sub‑range and then merging the results, avoiding a full filesort. This works best when the IN list is short; a very long list may cause the optimizer to fall back to a table scan.

Option 3: Use a Covering Index to Reduce Filesort Cost

When filesort cannot be eliminated, limit the amount of data sorted by fetching only the indexed columns first:

SELECT * FROM t_order
WHERE id IN (
  SELECT id FROM t_order
  WHERE status = 1 AND channel > 2
  ORDER BY created_at DESC
  LIMIT 20
);

The inner query uses the index to produce a small, already‑sorted set of primary keys; the outer query then retrieves the full rows by primary key, dramatically reducing the sorting workload.

Takeaway

A composite index’s ability to avoid filesort depends not merely on whether the needed columns appear in the index, but on whether earlier predicates preserve the ordering of later columns. Range conditions, mismatched ORDER BY order, and mixed ASC/DESC directions can all break the index’s ordered‑ness, forcing MySQL to perform an extra sort.