MongoDB not using Index on simple find-CodePudding

I have a collection called "EN" and I created an index as follow:

db.EN.createIndex( { "Prod_id": 1 } );

When I run db.EN.getIndexes() I get this:

[{   "v": 2,   "key": {
    "_id": 1   },   "name": "_id_" }, {   "v": 2,   "key": {
    "Prod_id": 1   },   "name": "Prod_id_1" }]

However, when I run the following query:

    db.EN.find({'Icecat-interface.Product.@Prod_id':'ABCD'})
      .explain()

I get this:

{   "explainVersion": "1",   "queryPlanner": {
    "namespace": "Icecat.EN",
    "indexFilterSet": false,
    "parsedQuery": {
      "ICECAT-interface.Product.Prod_id": {
        "$eq": "ABCD"
      }
    },
    "queryHash": "D12BE22E",
    "planCacheKey": "9F077ED2",
    "maxIndexedOrSolutionsReached": false,
    "maxIndexedAndSolutionsReached": false,
    "maxScansToExplodeReached": false,
    "winningPlan": {
      "stage": "COLLSCAN",
      "filter": {
        "ICECAT-interface.Product.Prod_id": {
          "$eq": "ABCD"
        }
      },
      "direction": "forward"
    },
    "rejectedPlans": []   },   "command": {
    "find": "EN",
    "filter": {
      "ICECAT-interface.Product.Prod_id": "ABCD"
    },
    "batchSize": 1000,
    "projection": {},
    "$readPreference": {
      "mode": "primary"
    },
    "$db": "Icecat"   },   "serverInfo": {

It's using COLLSCAN instead of the index, why is this happening?

MongoDB version is 5.0.9-8

Thanks

EDIT (and solution)

It turns that the field name has "@" in front and the index was created without this character so was not picking it up at all.

Once I created a new index using the field name as it was supposed to be it worked OK.

It was interesting though to see how indexing works and best practices

CodePudding user response：

Your find operation is defined as

.find({'Icecat-interface.Product.@Prod_id':'ABCD'})

What is Icecat-interface.Product.@?

The parsedQuery in the explain output confirms that MongoDB is attempting to look for a document that has has a value of "ABCD" for a different field name than the one you have aindexed. From the explain you've provided, that field name is "ICECAT-interface.Product.Prod_id". As the field name being queried and the one that is indexed are different, MongoDB cannot use the index to perform the operation.

Marginally related, the @ character that is used in the find is absent in the explain output. This appears to because the actual operation that was used to generate the explain was slightly different. This is also noticeable by the fact that the explain include a batchSize of 1000 which is absent in the operation that was shown as the one being explained.

Depending on what the Icecat-interface.Product.@ prefix is supposed to be, the solution is probably to simply remove that from the query predicate in the find itself.

Edit to respond to the comment and the edit to the question. Regarding the comment first:

When I run this: .find({'Prod_id':'ABCD'}) it uses COLLSCAN which to me is wrong, as I have an index on that field, unless I'm missing something here

MongoDB will look to use an index if its first key is used by the query. So an index on { y: 1 } would not be eligible for use by a query of .find({ x: 1}). Similarly to a generic x and y example, Icecat-interface.Product.Prod_id and Prod_id are different field names. So if you query on one but only an index on the other exists, then a collection scan is the only way for the database to execute the query.

This then overlaps some with the edit to the question. In the edited question the new explain plan shows the database successfully using an index. However, that index is { "ICECAT-interface.Product.Prod_id": 1 } which is not the index that you originally show being created or present on the collection ({ "Prod_id": 1 }).

Moreover, you also mention that you "don't get any result back, even with products I know are in the DB". Which field in the database contains the value that you are searching on ('ABCD')? This is going to directly inform what results you get back and what index is used to find the results. Remember that you can search on any arbitrary field in MongoDB, even if it doesn't exist in the database.

I would recommend some extra attention be paid to the namespaces and field names that are being used. Unless this { "ICECAT-interface.Product.Prod_id": 1 } index was created after the db.EN.getIndexes() output was gathered, you may be inadvertently connecting to different systems or namespaces since that index is definitely present somewhere.

Based on your live comments while I'm writing this, seems like you've solved the field name mystery.