1

It takes around 6min to get the result from MongoDB, when I use the following aggregate query.

db.barcodes.aggregate([
    {
        $lookup: {
            from: 'company',
            localField: 'company',
            foreignField: '_id',
            as: 'company'
        }
    },
    {
        $match: {
            'company.name': 'ABCd'
        }
    }
]);

I have two collections in my DB, company and barcode. If I search with text 'ABC' (instead of 'ABCd', company name 'ABC' already exists in the DB) it takes only 0.05Sec to complete the result.

Total 42,14,301 documents in barcode collection and 2 documents in company collection.

Sample documents

Company

{ 
    "_id" : ObjectId("615dd7873c4f710b71438772"), 
    "name" : "ABC", 
    "isActive" : true
}

Barcode

{ 
    "_id" : ObjectId("615dd8ff3c4f710b71438773"), 
    "barcode" : "1", 
    "company" : ObjectId("615dd7873c4f710b71438772"), 
    "comment" : "text 1"
}

Indexed fields

  • company._id
  • company.name
  • company.isActive
  • barcode.company
  • barcode._id

Mongo clients used: Studio 3t and MongoDB CLI

Output of explain

{ 
    "stages" : [
        {
            "$cursor" : {
                "query" : {

                }, 
                "queryPlanner" : {
                    "plannerVersion" : 1.0, 
                    "namespace" : "diet.barcodes", 
                    "indexFilterSet" : false, 
                    "parsedQuery" : {

                    }, 
                    "winningPlan" : {
                        "stage" : "COLLSCAN", 
                        "direction" : "forward"
                    }, 
                    "rejectedPlans" : [

                    ]
                }
            }
        }, 
        {
            "$lookup" : {
                "from" : "company", 
                "as" : "company", 
                "localField" : "company", 
                "foreignField" : "_id"
            }
        }, 
        {
            "$match" : {
                "company.name" : {
                    "$eq" : "ABCd"
                }
            }
        }
    ], 
    "ok" : 1.0
}
0
0

Your winning query is running against diet.barcodes and is doing a COLLSCAN because it doesn't have an index.

Create an index of that collection.

6
  • How to create an index for a collection. i created index for company, db.barcodes.createIndex({company:1})
    – Albert
    Oct 11 at 19:29
  • Please share the output of: > db.barcodes.findOne() > db.company.findOne()
    – zelmario
    Oct 11 at 19:47
  • db.barcodes.findOne() >{ "_id" : ObjectId("615dd8ff3c4f710b71438773"), "barcode" : "1", "company" : ObjectId("615dd7873c4f710b71438772"), "comment" : "text 1" } db.company.findOne(){ "_id" : ObjectId("615dd7873c4f710b71438772"), "name" : "ABC", "isActive" : true }
    – Albert
    Oct 12 at 4:29
  • Try to create an index of barcodes._id and run the query again, if it possible with the explaing argument.
    – zelmario
    Oct 12 at 9:58
  • index for barcodes._id is already added
    – Albert
    Oct 12 at 11:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.