CODEWITHSUNDEEP
mongodbindexing
Michael Bailey
Michael Bailey

Reputation: 411

Optimizing mongod $and query with an $or

I'm trying to optimize this query and I can't get it to use an index. With just the $or query the 3 indexes on the $or fields work fine. However if I add the $and in there then it only uses the actor._id index.

{
  '$and': [
    {
      '$or': [
        {
          'object._id': ObjectId('512fbef8a989b28ddb5cdf93')
        },
        {
          'target._id': ObjectId('512fbef8a989b28ddb5cdf93')
        },
        {
          'target.user_id': ObjectId('512fbef8a989b28ddb5cdf93')
        }
      ]
    },
    {
      'actor._id': {
        '$ne': ObjectId('512fbef8a989b28ddb5cdf93')
      }
    }
  ]
}

Can someone either help me optimize this query for the better or optimize the index?

Thanks in advance

As requested here are the explain results for each.

With the And

{
"cursor" : "BtreeCursor actor._id_1 multi",
"isMultiKey" : false,
"n" : 2464,
"nscannedObjects" : 1761201,
"nscanned" : 1761202,
"nscannedObjectsAllPlans" : 1761201,
"nscannedAllPlans" : 1761202,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 3,
"nChunkSkips" : 0,
"millis" : 6215,
"indexBounds" : {
    "actor._id" : [
        [
            {
                "$minElement" : 1
            },
            ObjectId("512fbef8a989b28ddb5cdf93")
        ],
        [
            ObjectId("512fbef8a989b28ddb5cdf93"),
            {
                "$maxElement" : 1
            }
        ]
    ]
},
"server" : "Michaels-MacBook-Pro.local:27017"

}

And Without the $and

{
"clauses" : [
    {
        "cursor" : "BtreeCursor object._id_1",
        "isMultiKey" : false,
        "n" : 388,
        "nscannedObjects" : 388,
        "nscanned" : 388,
        "nscannedObjectsAllPlans" : 388,
        "nscannedAllPlans" : 388,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "millis" : 4,
        "indexBounds" : {
            "object._id" : [
                [
                    ObjectId("512fbef8a989b28ddb5cdf93"),
                    ObjectId("512fbef8a989b28ddb5cdf93")
                ]
            ]
        }
    },
    {
        "cursor" : "BtreeCursor target._id_1",
        "isMultiKey" : false,
        "n" : 77,
        "nscannedObjects" : 375,
        "nscanned" : 375,
        "nscannedObjectsAllPlans" : 375,
        "nscannedAllPlans" : 375,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "millis" : 3,
        "indexBounds" : {
            "target._id" : [
                [
                    ObjectId("512fbef8a989b28ddb5cdf93"),
                    ObjectId("512fbef8a989b28ddb5cdf93")
                ]
            ]
        }
    },
    {
        "cursor" : "BtreeCursor target.user_id_1",
        "isMultiKey" : false,
        "n" : 2554,
        "nscannedObjects" : 2554,
        "nscanned" : 2554,
        "nscannedObjectsAllPlans" : 2554,
        "nscannedAllPlans" : 2554,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "millis" : 23,
        "indexBounds" : {
            "target.user_id" : [
                [
                    ObjectId("512fbef8a989b28ddb5cdf93"),
                    ObjectId("512fbef8a989b28ddb5cdf93")
                ]
            ]
        }
    }
],
"n" : 3019,
"nscannedObjects" : 3317,
"nscanned" : 3317,
"nscannedObjectsAllPlans" : 3317,
"nscannedAllPlans" : 3317,
"millis" : 31,
"server" : "Michaels-MacBook-Pro.local:27017"

}

Upvotes: 0

Views: 425

Answers (1)

Salvador Dali
Salvador Dali

Reputation: 222751

I do not understand why do you need this $and

{
  '$and': [{
      '$or': [
        {'object._id': ObjectId('512fbef8a989b28ddb5cdf93')},
        {'target._id': ObjectId('512fbef8a989b28ddb5cdf93')},
        {'target.user_id': ObjectId('512fbef8a989b28ddb5cdf93')}
      ]
    },{
      'actor._id': {'$ne': ObjectId('512fbef8a989b28ddb5cdf93')}
    }
  ]
}

In my opinion you can just do

{
  'actor._id': {'$ne': ObjectId('512fbef8a989b28ddb5cdf93')},
   $or': [
        {'object._id': ObjectId('512fbef8a989b28ddb5cdf93')},
        {'target._id': ObjectId('512fbef8a989b28ddb5cdf93')},
        {'target.user_id': ObjectId('512fbef8a989b28ddb5cdf93')}
      ]
}

Another thing is that using the index on the field that you are searching by $ne query is not a good idea. So I would rather remove the index from that field.

Upvotes: 1

Related Questions