1

I have a collection that has 30 million documents, and I am trying to do a live search that selects the documents by a keyword and sorts them in a certin matter (it shows first users that have a picture and that have the claim field set to TRUE).

This is how my document looks like:

{ 
    "_id" : ObjectId("5b7c23a79dfbc868d2c0f805"), 
    "business" : {
        "name" : "Cartersville Garage Door",
        "nameArr" : [
            "cartersville", 
            "garage", 
            "door"
        ],
        "slug" : "cartersville-garage-door", 
        "img" : {
            "profile" : {
                "full" : "5b7c23a79dfbc868d2c0f805_1000x1000_cartersville-garage-door-united-states-georgia_1.jpg", 
                "thumb" : "5b7c23a79dfbc868d2c0f805_160x160_cartersville-garage-door-united-states-georgia_1.jpg", 
                "tiny" : "5b7c23a79dfbc868d2c0f805_40x40_cartersville-garage-door-united-states-georgia_1.jpg"
            },
        },
    },
}

This is how my query looks like, which takes 53 seconds to finish, if I remove the sorting part, then it's very fast few milliseconds.

db.getCollection("users").find(
    { 
        "business.nameArr" : {
            "$regex" : "^car"
        }
    }
).sort(
    { 
        "business.claim" : -1, 
        "business.img.profile.full" : -1
    }
).limit(10);

I have an index on:

{ 
    "key" : {
        "business.nameArr" : NumberInt(1), 
        "business.claim" : NumberInt(-1), 
        "business.img.profile.full" : NumberInt(-1)
    }, 
    "name" : "nameArr-claim-profileImg", 
    "background" : true, 
}

This is the explain():

{ 
    "queryPlanner" : {
        "plannerVersion" : NumberInt(1), 
        "namespace" : "bunity.users", 
        "indexFilterSet" : false, 
        "parsedQuery" : {
            "business.nameArr" : {
                "$regex" : "^car"
            }
        }, 
        "winningPlan" : {
            "stage" : "SORT", 
            "sortPattern" : {
                "business.claim" : -1.0, 
                "business.img.profile.full" : -1.0
            }, 
            "limitAmount" : NumberInt(10), 
            "inputStage" : {
                "stage" : "SORT_KEY_GENERATOR", 
                "inputStage" : {
                    "stage" : "FETCH", 
                    "inputStage" : {
                        "stage" : "IXSCAN", 
                        "keyPattern" : {
                            "business.nameArr" : NumberInt(1), 
                            "business.claim" : NumberInt(-1), 
                            "business.img.profile.full" : NumberInt(-1)
                        }, 
                        "indexName" : "nameArr-claim-profileImg", 
                        "isMultiKey" : true, 
                        "multiKeyPaths" : {
                            "business.nameArr" : [
                                "business.nameArr"
                            ], 
                            "business.claim" : [

                            ], 
                            "business.img.profile.full" : [

                            ]
                        }, 
                        "isUnique" : false, 
                        "isSparse" : true, 
                        "isPartial" : false, 
                        "indexVersion" : NumberInt(2), 
                        "direction" : "forward", 
                        "indexBounds" : {
                            "business.nameArr" : [
                                "[\"car\", \"cas\")", 
                                "[/^car/, /^car/]"
                            ], 
                            "business.claim" : [
                                "[MaxKey, MinKey]"
                            ], 
                            "business.img.profile.full" : [
                                "[MaxKey, MinKey]"
                            ]
                        }
                    }
                }
            }
        }, 
        "rejectedPlans" : [

        ]
    }, 
    "executionStats" : {
        "executionSuccess" : true, 
        "nReturned" : NumberInt(10), 
        "executionTimeMillis" : NumberInt(53433), 
        "totalKeysExamined" : NumberInt(807173), 
        "totalDocsExamined" : NumberInt(790436), 
        "executionStages" : {
            "stage" : "SORT", 
            "nReturned" : NumberInt(10), 
            "executionTimeMillisEstimate" : NumberInt(53286), 
            "works" : NumberInt(807186), 
            "advanced" : NumberInt(10), 
            "needTime" : NumberInt(807175), 
            "needYield" : NumberInt(0), 
            "saveState" : NumberInt(6308), 
            "restoreState" : NumberInt(6308), 
            "isEOF" : NumberInt(1), 
            "invalidates" : NumberInt(0), 
            "sortPattern" : {
                "business.claim" : -1.0, 
                "business.img.profile.full" : -1.0
            }, 
            "memUsage" : NumberInt(16315), 
            "memLimit" : NumberInt(33554432), 
            "limitAmount" : NumberInt(10), 
            "inputStage" : {
                "stage" : "SORT_KEY_GENERATOR", 
                "nReturned" : NumberInt(790436), 
                "executionTimeMillisEstimate" : NumberInt(53066), 
                "works" : NumberInt(807175), 
                "advanced" : NumberInt(790436), 
                "needTime" : NumberInt(16738), 
                "needYield" : NumberInt(0), 
                "saveState" : NumberInt(6308), 
                "restoreState" : NumberInt(6308), 
                "isEOF" : NumberInt(1), 
                "invalidates" : NumberInt(0), 
                "inputStage" : {
                    "stage" : "FETCH", 
                    "nReturned" : NumberInt(790436), 
                    "executionTimeMillisEstimate" : NumberInt(51616), 
                    "works" : NumberInt(807174), 
                    "advanced" : NumberInt(790436), 
                    "needTime" : NumberInt(16737), 
                    "needYield" : NumberInt(0), 
                    "saveState" : NumberInt(6308), 
                    "restoreState" : NumberInt(6308), 
                    "isEOF" : NumberInt(1), 
                    "invalidates" : NumberInt(0), 
                    "docsExamined" : NumberInt(790436), 
                    "alreadyHasObj" : NumberInt(0), 
                    "inputStage" : {
                        "stage" : "IXSCAN", 
                        "nReturned" : NumberInt(790436), 
                        "executionTimeMillisEstimate" : NumberInt(1753), 
                        "works" : NumberInt(807174), 
                        "advanced" : NumberInt(790436), 
                        "needTime" : NumberInt(16737), 
                        "needYield" : NumberInt(0), 
                        "saveState" : NumberInt(6308), 
                        "restoreState" : NumberInt(6308), 
                        "isEOF" : NumberInt(1), 
                        "invalidates" : NumberInt(0), 
                        "keyPattern" : {
                            "business.nameArr" : NumberInt(1), 
                            "business.claim" : NumberInt(-1), 
                            "business.img.profile.full" : NumberInt(-1)
                        }, 
                        "indexName" : "nameArr--claim-profileImg", 
                        "isMultiKey" : true, 
                        "multiKeyPaths" : {
                            "business.nameArr" : [
                                "business.nameArr"
                            ], 
                            "business.claim" : [

                            ], 
                            "business.img.profile.full" : [

                            ]
                        }, 
                        "isUnique" : false, 
                        "isSparse" : true, 
                        "isPartial" : false, 
                        "indexVersion" : NumberInt(2), 
                        "direction" : "forward", 
                        "indexBounds" : {
                            "business.nameArr" : [
                                "[\"car\", \"cas\")", 
                                "[/^car/, /^car/]"
                            ], 
                            "business.claim" : [
                                "[MaxKey, MinKey]"
                            ], 
                            "business.img.profile.full" : [
                                "[MaxKey, MinKey]"
                            ]
                        }, 
                        "keysExamined" : NumberInt(807173), 
                        "seeks" : NumberInt(2), 
                        "dupsTested" : NumberInt(807172), 
                        "dupsDropped" : NumberInt(16736), 
                        "seenInvalidated" : NumberInt(0)
                    }
                }
            }
        }, 
        "allPlansExecution" : [

        ]
    }, 

}

Any thoughts how to make this query run faster?

8
  • Could you try { "business.nameArr": { $gte: "car", $lt : "cas" } } ? Aug 27, 2018 at 9:49
  • @GerardH.Pille why "cas"? this input is manually inserted by the user depending on what they search Aug 30, 2018 at 1:46
  • Take the input, replace last character by the next ascii: a->b, b->c, ..., y->z, z->{ Aug 30, 2018 at 1:56
  • @GerardH.Pille oh alright, also in my initial query I have a regex "^car" (it checks the begninig of the word), but this just checks the whole word if it matches. Aug 30, 2018 at 1:59
  • @GerardH.Pille It didn't work, it returns other documents that have nothing to do with the keyword, basically it returns all results. Aug 30, 2018 at 3:33

1 Answer 1

0

A pure string compare may prove faster than the regex, and allow using your index:

db.getCollection("users").find(
    { 
        "business.nameArr": {
           $elemMatch: { $gte: "car", $lt: "cas" }
        }
    }
).sort(
    { 
        "business.claim" : -1, 
        "business.img.profile.full" : -1
    }
).limit(10);

But your statistics show that you still have to sort 800.000 records, before you select the first 10. Perhaps this sort could be eliminated, if the sort would correspond to the index.

db.getCollection("users").find(
    { 
        "business.nameArr": {
           $elemMatch: { $gte: "car", $lt: "cas" }
        }
    }
).sort(
    { 
        "business.nameArr : 1,
        "business.claim" : -1, 
        "business.img.profile.full" : -1
    }
).limit(10);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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