Mongo not using index

Question

I have the following indexes within a collection:

db.JobStatusModel.getIndexes()
[
    {
        "v" : 1,
        "key" : {
            "_id" : 1
        },
        "name" : "_id_",
        "ns" : "jobs.JobStatusModel"
    },
    {
        "v" : 1,
        "key" : {
            "peopleId" : 1,
            "jobId" : 1
        },
        "name" : "peopleId_jobId_compounded",
        "ns" : "jobs.JobStatusModel"
    },
    {
        "v" : 1,
        "key" : {
            "jobId" : 1
        },
        "name" : "jobId_1",
        "ns" : "jobs.JobStatusModel",
        "background" : true
    },
    {
        "v" : 1,
        "key" : {
            "peopleId" : 1,
            "disInterested" : 1
        },
        "name" : "peopleId_1_disInterested_1",
        "ns" : "jobs.JobStatusModel",
        "background" : true
    }
]

Trying to work out some slow running queries running against the compound indexes, however, even simple queries aren't making use of indexes:

db.JobStatusModel.find({ jobId : '1f940601ff7385931ec04dca88c853dd' }).explain(true)
{
    "cursor" : "BtreeCursor jobId_1",
    "isMultiKey" : false,
    "n" : 221,
    "nscannedObjects" : 221,
    "nscanned" : 221,
    "nscannedObjectsAllPlans" : 221,
    "nscannedAllPlans" : 221,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 1,
    "nChunkSkips" : 0,
    "millis" : 1,
    "indexBounds" : {
        "jobId" : [
            [
                "1f940601ff7385931ec04dca88c853dd",
                "1f940601ff7385931ec04dca88c853dd"
            ]
        ]
    },
    "allPlans" : [
        {
            "cursor" : "BtreeCursor jobId_1",
            "isMultiKey" : false,
            "n" : 221,
            "nscannedObjects" : 221,
            "nscanned" : 221,
            "scanAndOrder" : false,
            "indexOnly" : false,
            "nChunkSkips" : 0,
            "indexBounds" : {
                "jobId" : [
                    [
                        "1f940601ff7385931ec04dca88c853dd",
                        "1f940601ff7385931ec04dca88c853dd"
                    ]
                ]
            }
        }
    ],
    "server" : "mongo3.pilot.dice.com:27017",
    "filterSet" : false,
    "stats" : {
        "type" : "FETCH",
        "works" : 222,
        "yields" : 1,
        "unyields" : 1,
        "invalidates" : 0,
        "advanced" : 221,
        "needTime" : 0,
        "needFetch" : 0,
        "isEOF" : 1,
        "alreadyHasObj" : 0,
        "forcedFetches" : 0,
        "matchTested" : 0,
        "children" : [
            {
                "type" : "IXSCAN",
                "works" : 222,
                "yields" : 1,
                "unyields" : 1,
                "invalidates" : 0,
                "advanced" : 221,
                "needTime" : 0,
                "needFetch" : 0,
                "isEOF" : 1,
                "keyPattern" : "{ jobId: 1.0 }",
                "isMultiKey" : 0,
                "boundsVerbose" : "field #0['jobId']: [\"1f940601ff7385931ec04dca88c853dd\", \"1f940601ff7385931ec04dca88c853dd\"]",
                "yieldMovedCursor" : 0,
                "dupsTested" : 0,
                "dupsDropped" : 0,
                "seenInvalidated" : 0,
                "matchTested" : 0,
                "keysExamined" : 221,
                "children" : [ ]
            }
        ]
    }
}

as we can see from the output I am getting the "indexOnly" : false, from the output meaning it cannot just do an index scan even though my field is indexed. How can I ensure queries are running only against indexes?

Answer 1

even simple queries aren't making use of indexes:

Your query did use an index as indicated by the IXSCAN stage and index cursor ("cursor" : "BtreeCursor jobId_1",).

Trying to work out some slow running queries running against the compound indexes, however,

Based on the provided getIndexes() output, your query on the single field jobId only has one candidate index to consider: {jobId:1}. This query ran in 1 millisecond ("millis" : 1) and returned 221 documents looking at 221 index keys -- an ideal 1:1 hit ratio for key comparisons to matches.

The compound index of {peopleId:1, jobId:1} would only be considered if you also provided a peopleId value in your query. However, you could potentially create a compound index with these fields in the opposite order if you sometimes query solely on jobId but also frequently query on both peopleId and jobId. A compound index on {jobId:1, peopleId:1} would obviate the need for the {jobId:1} index since it could satisfy the same queries.

For more information see Create Indexes to Support Your Queries in the MongoDB manual and the blog post Optimizing MongoDB Compound Indexes.

Note: You haven't mentioned what version of MongoDB server you are using but the format of your explain() output indicates that you're running an older version of MongoDB that has reached End-of-Life (i.e. anything older than MongoDB 3.0 as at Jan-2017). I strongly recommend upgrading to a newer and supported version (eg. MongoDB 3.2 or 3.4) as there are significant improvements. End-of-Life server release series are no longer maintained and may potentially expose your application to known bugs and vulnerabilities that have been addressed in subsequent production releases.

as we can see from the output I am getting the "indexOnly" : false, from the output meaning it cannot just do an index scan even though my field is indexed. How can I ensure queries are running only against indexes?

The indexOnly value will only be true in the special case of a covered query. A covered query is one where all of the fields in the query are part of an index and all of the fields projected in the results are in the same index. Typically indexed queries are not covered: index lookups are used to find matching documents which are then retrieved and filtered to the fields requested in the query projection.

Answer 2

In order to be sure you get indexOnly you need to return only those fields from the index, use projection:

db.collection.find( <query filter>, <projection> )

db.JobStatusModel.find({ jobId : '1f940601ff7385931ec04dca88c853dd' }, {jobId:1, _id:0})