Mongodb – How to make an index for an OR query in MongoDB

indexmongodbnosqlperformancequery-performance

So I have a collection with 55 million documents or so. I've enabled system profiling to check for slow queries and I do have a few every now and again.

Example:

{
        "$or" : [
                {
                        "association.ownerId" : ObjectId("55e5c40fe4b03984a0528f89")
                },
                {
                        "association.organisationId" : ObjectId("55e6d92ee4b010a9ebff510d")
                }
        ],
        "visitorId" : 384975548,
        "created" : {
                "$gte" : ISODate("2016-09-18T22:00:00Z"),
                "$lte" : ISODate("2016-09-19T22:00:00Z")
        }
}

.explain() still says it won't use indices only for this query

{
        "cursor" : "BtreeCursor visitorId_1",
        "isMultiKey" : false,
        "n" : 13,
        "nscannedObjects" : 5163,
        "nscanned" : 5163,
        "nscannedObjectsAllPlans" : 44745,
        "nscannedAllPlans" : 44993,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "nYields" : 1621,
        "nChunkSkips" : 0,
        "millis" : 41201,
        "indexBounds" : {
                "visitorId" : [
                        [
                                2962347,
                                2962347
                        ]
                ]
        },
        "server" : "<censored_host>:27017",
        "filterSet" : false
}

Here are some indices I have:

{
            "v" : 1,
            "key" : {
                    "association.ownerId" : 1,
                    "association.organisationId" : 1,
                    "visitorId" : 1,
                    "created" : 1
            },
            "name" : "association.ownerId_1_association.organisationId_1_visitorId_1_created_1",
        "ns" : "censored_dbname.events"
},
{
        "v" : 1,
        "key" : {
                "association.ownerId" : 1,
                "visitorId" : 1,
                "created" : 1
        },
        "name" : "association.ownerId_1_visitorId_1_created_1",
        "ns" : "censored_dbname.events",
        "background" : true
},
{
        "v" : 1,
        "key" : {
                "association.organisationId" : 1,
                "visitorId" : 1,
                "created" : 1
        },
        "name" : "association.organisationId_1_visitorId_1_created_1",
        "ns" : "censored_dbname.events",
        "background" : true
}

Anyone have a clue how I can improve this query?

Best Answer

You're hitting SERVER-13732.

For the query planner to properly consider indexes and apply the appropriate index bounces, the query will need to be reformatted to the following structure:

{
       "$or" : [
            {
                    "association.ownerId" : ObjectId("55e5c40fe4b03984a0528f89"),
                    "visitorId" : 384975548,
                    "created" : {
                           "$gte" : ISODate("2016-09-18T22:00:00Z"),
                           "$lte" : ISODate("2016-09-19T22:00:00Z")
                    }
            },
            {
                    "association.organisationId" : ObjectId("55e6d92ee4b010a9ebff510d"),
                    "visitorId" : 384975548,
                    "created" : {
                           "$gte" : ISODate("2016-09-18T22:00:00Z"),
                           "$lte" : ISODate("2016-09-19T22:00:00Z")
                    }
            }
    ]
}

It should then be able to use the combination of { "association.ownerId" : 1, "visitorId" : 1, "created" : 1 } and { "association.organisationId" : 1, "visitorId" : 1, "created" : 1 }. That being said, you should try testing the re-structured query pattern first using .explain().

Related Question