Why are any objects being scanned here?

Question

I have an index:

{indices.textLc:1, group:1, lc:1, wordCount:1, pattern:1, clExists:1} 

and Morphia generates queries like:

{
    $and: [{
        lc: "eng"
    },
    {
        $or: [{
            group: "cn"
        },
        {
            group: "all"
        }]
    },
    {
        "indices.textLc": {
            $in: ["media strengthening", "strengthening", "media"]
        }
    },
    {
        wordCount: {
            $gte: 1
        }
    },
    {
        wordCount: {
            $lte: 2
        }
    }]
}

and explain gives:

{
    "cursor" : "BtreeCursor indices.textLc_1_group_1_lc_1_wordCount_1_pattern_1_clExists_1 multi",
    "nscanned" : 20287,
    "nscannedObjects" : 20272,
    "n" : 22,
    "millis" : 677,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "isMultiKey" : true,
    "indexOnly" : false,
    "indexBounds" : {
        "indices.textLc" : [
            [
                "media",
                "media"
            ],
            [
                "media strengthening",
                "media strengthening"
            ],
            [
                "strengthening",
                "strengthening"
            ]
        ],
        "group" : [
            [
                {
                    "$minElement" : 1
                },
                {
                    "$maxElement" : 1
                }
            ]
        ],
        "lc" : [
            [
                "eng",
                "eng"
            ]
        ],
        "wordCount" : [
            [
                1,
                1.7976931348623157e+308
            ]
        ],
        "pattern" : [
            [
                {
                    "$minElement" : 1
                },
                {
                    "$maxElement" : 1
                }
            ]
        ],
        "clExists" : [
            [
                {
                    "$minElement" : 1
                },
                {
                    "$maxElement" : 1
                }
            ]
        ]
    }

Firstly, I don't understand why any scanning is required since everything is available in the index. More specifically, why does the wordCount part of the indexBounds not look like:

 "wordCount" : [
            [
                1,
                2
            ]
        ],

Update 2012-03-20: If it helps explain anything, I'm running MongoDB 2.0.3

Answer 1

Every field in your query being available in your compound index says very little about whether or not it can use your one index for every clause in your query. There are a few things to consider :

• With the exception of top-level $or clauses which can use an index per clause every MongoDB query can use at most one index.
• Compound indexes only work if each subsequent field in the compound can be used in order, meaning your query allows for filtering on the first index field first, the second next and so on. SO if you have an index {a:1, b:1} a query {b:"Hi!"} would not use the index even though the field is in the compound index.

Now, the reason your query requires a scan is because your index can only optimize the query execution plan for the "indices.textLc" field (your first index field) and in this particular case "lc" because it's a seperate clause in your $and.

The "wordCount" part of the explain should actually read :

 "wordCount" : [
         [
                 1,
                 2
         ]
 ]

I just tested it and it does on my machine so I think something's going wrong with your Morphia/mapping solution there.

Compound indexes and complicated queries such as yours are a tricky subject. I don't have time now to look at your query and index and see if it can be optimized. I'll revisit tonight and help you out if I can.