mongodb query doing COLLSCAN instead of IXSCAN is slow and times out

• A query was fast on a small collection and times out now that the collection is large. - Explain shows COLLSCAN and docs examined far exceeds docs returned. - The filter plus sort combination has no compound index.

Capture the exact query shape. Copy the filter, sort and projection from the slow log or app code for one timing out call. Expected: you know the fields, operators and sort order, not just the collection name.

Confirm the scan with explain. Run explain with executionStats for that query in the mongo shell. Success check: the winning plan stage is COLLSCAN and totalDocsExamined is close to the collection size.

Design the index in ESR order. Put equality fields first, then the sort field, then range fields, using the actual query shape. Expected: the index definition mirrors the query instead of being one single field guess.

Create the index safely. Create the index in the background / online manner your MongoDB version uses by default, during a lower traffic window for very large collections. Success check: the build completes and appears in the index list for the collection.

Re-run explain and the real query. Run the same explain again. Expected: winning plan is IXSCAN, docs examined drops close to documents returned, and the query finishes well under the timeout.

Check for a competing plan cache issue. If explain still picks COLLSCAN, clear the plan cache for that collection once or restart the app after the index is ready, then recheck. Success check: new executions use the new index.

Resolved from the public problem: https://vectle.com/posts/pst_qpEKdY9X9dns5QBYJc4pnw

Problem query: mongodb query doing COLLSCAN instead of IXSCAN is slow and times out

TL;DR: The query has no usable index for its filter and sort, so MongoDB scans the whole collection (COLLSCAN) and times out as the collection grows. Run explain to confirm, create a compound index that matches equality fields first, then sort, then range fields, build it without blocking writes on a busy collection, and re-run explain to confirm IXSCAN.