Your query uses an index and is still slow

You added the index. explain() says IXSCAN. The query still takes 400 ms.

This is the point where a lot of people conclude the index “did not help” and add another one. Often the index is fine and is being used, and the plan is telling you so, but the useful number is not the stage name. It is the ratio between two counts further down the output.

The two numbers

Run the query with executionStats, not the default:

db.orders.find({ status: 'shipped', region: 'EU' })
  .sort({ createdAt: -1 })
  .limit(50)
  .explain('executionStats');

Find these in the result:

{
  "executionStats": {
    "nReturned": 50,
    "totalKeysExamined": 84210,
    "totalDocsExamined": 84210,
    "executionTimeMillis": 412
  }
}

nReturned is what you asked for. totalDocsExamined is what the server read to find it. Here it read 84,210 documents to hand back 50.

The target is totalDocsExamined close to nReturned. A well-served query examines roughly what it returns. When the gap is three orders of magnitude, the index narrowed the search a little and then the server did the rest by hand, and the stage still says IXSCAN the whole time.

Why an index gets used and still leaves work

An index on { status: 1 } alone can serve that query. It finds every shipped order, which might be a third of the collection, and then the server has to fetch each of those documents to check region and to sort them.

The plan shows that as a FETCH above the IXSCAN, and often a SORT above that:

SORT
  └── FETCH
        └── IXSCAN  { status: 1 }

Three things to read there:

• IXSCAN at the bottom means an index was used to start.
• FETCH above it means the index did not carry every field the query needed, so the server went to the documents.
• SORT above that means the ordering was done in memory after the fact, rather than being handed over already sorted by the index.

Each of those is a place work happened. The stage name at the bottom tells you none of it.

What usually fixes it

A compound index that covers the filter and the sort, in that order:

db.orders.createIndex({ status: 1, region: 1, createdAt: -1 });

Re-run the explain. What you want to see:

{
  "nReturned": 50,
  "totalKeysExamined": 50,
  "totalDocsExamined": 50,
  "executionTimeMillis": 2
}

And the plan collapses:

FETCH
  └── IXSCAN  { status: 1, region: 1, createdAt: -1 }

No SORT, because the index already holds the rows in that order. totalDocsExamined equal to nReturned, because the index narrowed it to exactly the fifty.

The field order in that index is not arbitrary and getting it wrong puts the SORT back. That is a separate problem with its own signature in the plan.

The three-line version

When a query is slow and the plan says IXSCAN:

1. Read totalDocsExamined against nReturned. Close together is healthy; orders of magnitude apart is the problem.
2. Look for a SORT stage. If there is one, the index is not serving the ordering.
3. Look for a FETCH with a large gap under it. The index found candidates, not answers.

If you want to skip reading the JSON, paste the output into the explain visualizer. It draws the stage tree, works out the examined against returned ratio, and names the compound index that would remove the collection scan or the in-memory sort. It runs in the browser and nothing you paste is uploaded, though it is still worth redacting filter values if they carry anything sensitive.