1

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?

0

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().

4
  • I'm looking for an index to the query I have, not a new query. – Sam Sep 26 '16 at 11:34
  • 1
    The query I recommend above should be logically the same query. The indexes you have are ideal. However, assuming you're using MongoDB 2.6 or later, the query planner won't be able to use them efficiently with the query structured the way it is. Try doing a .explain() on the reformatted query above and compare the output with your query. – Adam Harrison Sep 26 '16 at 20:24
  • It is logically the same, I'm not arguing that, but that wasn't my question. I'd change it if I could, but that's legacy software, so I can't. In the newer versions, this query has been improved and works fine. But for now, that's all I have to work with. Besides, although the query can be improved, I find it a bit ridiculous that there would be no appropriate index for a query like that. If MongoDB is too stupid to handle even something like this, then for future software I might need to reconsider my database choice. – Sam Sep 27 '16 at 9:56
  • With those restrictions in mind, you may want to try creating the index { 'visitorId' : 1, 'created' : 1 } and testing how its used. In my testing on a smaller data set, it is able to apply index bounds on both of those filters in the query, which should offer some improvement. – Adam Harrison Sep 30 '16 at 0:35

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.