Regarding ExecutionStats

Hi ,

I hope ,all are doing well .

I have a collection with millions of records. and i am trying to fetch using using nearSphere. but one thing that i observed that in ExecutionStats that totalKeysExamined scan is very high with respect to totalDocsExamined. My Query is given below -

db.getCollection('directorypage').find(

 {
     "appId": "c6979f9b230f","dirPageId": "services_1539646679172_5","status": 1,
     "$or": [
     {"heading": {"$regex": ".*Waverly .*","$options": "i"}},
     {"summary": {"$regex": ".*Waverly .*","$options": "i"}},
     {"body": {"$regex": ".*Waverly .*","$options": "i"}}
     ],
     "loc": {
           "$nearSphere": {
                  "$geometry": {
                   "type": "Point",
                   "coordinates": [
                                    -85.84142,
                                     38.130725
                                  ]
                      },
                   "$minDistance": 0,
                   "$maxDistance": 1609340
              }
            }
       }).skip(0).limit(10).explain('executionStats');

and i am attaching the result of ExecutionStats in attachment section.

Is this working fine?

Hi @Anuj_chauhan,

I would say that scanning 386k index keys and 16k docs to return 2 is far from optimized.

To help you with this query I would suggest provide the used index for it.

I would suggest the following index:

{ appId : 1, dirPageId : 1, status: 1, loc : "2dsphere"}

Thanks
Pavel

1 Like

Thanks for support ,


i have already applied this indexing. please look in the attached screeenshot.

Name - appId_dirPageId_status_loc
Key - {
    "appId" : 1,
    "dirPageId" : 1,
    "status" : 1,
    "catId" : 1,
    "heading" : 1,
    "summary" : 1,
    "body" : 1,
    "address" : 1,
    "latitude" : 1,
    "longitude" : 1,
    "loc" : "2dsphere"
}

Thanks

Hi @Anuj_chauhan,

This is not the same index it has 7 more fields between status and loc… The query will not use it as optimized.

This index seems to be insufficient as regex expression with unanchored and case insensitive data cannot use it.

Best
Pavel

1 Like