Find() not using index

Marc_Archenault · 2025-02-13T04:30:24.991Z

I created a local cluster and have being trying to learn how to do simple searches and have come across an issue with indexes not being used. I have a collection with simple docs in the form

{
“_id”: {
“$oid”: “67ad17c03e1c2b419bedbded”
},
“AAUID”: 1877,
“BIID”: 250,
“BID”: 813
}

I also have an index called AAUID which should cover “AAUID”

getIndexes() returns:

[
{
v: 2,
key: { _id: 1 },
name: ‘id’,
unique: true,
clustered: true
},
{ v: 2, key: { AAUID: 1 }, name: ‘AAUID’ }
]

But when I query using

find({AAUID:123}).explain()

I see “COLLSCAN”

{
“isCached”: false,
“stage”: “COLLSCAN”,
“filter”: {
“AAUID”: {
“$eq”: 123
}
},
“nReturned”: 2493,
“executionTimeMillisEstimate”: 4689,
“works”: 5000001,
“advanced”: 2493,
“needTime”: 4997507,
“needYield”: 0,
“saveState”: 322,
“restoreState”: 322,
“isEOF”: 1,
“direction”: “forward”,
“docsExamined”: 5000000
}

And the Explain Plan in Compass says “No index available for this query.”

Query returns in a few seconds which isn’t bad but I will have 300million docs in the future and at that rate it will be unusable.

Marc_Archenault · 2025-02-14T00:15:47.536Z

I created the same database/collection/index and data on Atlas and the query uses the index correctly.

steevej · 2025-02-14T13:37:59.734Z

In principle, unless you are a couple of version behind on your local cluster, it is the same code.

So, … suspense … I suspect you made a typo on the local cluster.

It is hard to pinpoint because your output is not showing any collection name nor database name.

The missing part of the explain plan that could help is the queryPlanner that shows the namespace. But it looks like the find was performed against the appropriate database/collection as we see with docsExamined:5_000_000.

The index might have been created in another database or collection.

Typos are often ignored as the possible cause of misbehaviour and they are most of the time. I have no data to backup the previous sentence so it is just an opinion.

Marc_Archenault · 2025-02-18T18:35:23.101Z

I created the index using “Create Index” in compass one the database and collection the data is in. I tried creating several indexes. Each index is on a different data in the collection. I then run my query in compass and all queries no matter the field come back as “No index available for this query.” If I do exactly the same using at atlas cluster it works as expected.

steevej · 2025-02-19T12:51:09.327Z

Sorry, but if not a typo I really do not know what could be the issue.

It is possible that the construction of the index is not completed yet. With 5_000_000 documents, it might take some time if mongod is running on small machine.