Why MongoDB cannot use a compound index that is much similar(not exact) to the query?

Question

Consider the below Mongo index strategy and the query,

Index:

db.collec.ensureIndex({a:1,b:1,c:1});

Query:

db.collec.find({"a":"valueA"},{"_id":0,"a":1,"c":1}).sort({"c":-1}).limit(150)

The explain on the above query returns:

/* 0 */
{
    "cursor" : "BtreeCursor a_1_b_1_c_1",
    "isMultiKey" : false,
    "n" : 150,
    "nscannedObjects" : 178,
    "nscanned" : 178,
    "nscannedObjectsAllPlans" : 279,
    "nscannedAllPlans" : 279,
    "scanAndOrder" : true,
    "indexOnly" : true,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 1,
    "indexBounds" : {
        "a" : [ 
            [ 
                "valueA", 
                "valueA"
            ]
        ],
        "b" : [ 
            [ 
                {
                    "$minElement" : 1
                }, 
                {
                    "$maxElement" : 1
                }
            ]
        ],
        "c" : [ 
            [ 
                {
                    "$minElement" : 1
                }, 
                {
                    "$maxElement" : 1
                }
            ]
        ]
    }
}

The question here is Its clearly indicated that the query runs completely on Index(as "indexOnly" : true). But why the "scanAndOrder" : true
According to Btree index model, c is at the tail of the index so it can be utilized to sort. No?

Why its not used?

Ragouzis

Answer 1

This is correct and also documented.

As to why: The index looks essentially like this tree:

• A: "value A"
  • B : "ABC"
    • C: 435
    • C: 678
  • B : "BCD"
    • C: 123
    • C: 993

As you can see, the ordering is correct and ascending, but if you'd take the values of c in-order without limiting to a subset of fixed b, you'd get [435, 678, 123, 993], which is not correct, so scanAndOrder is required.

Unfortunately, indexes without index intersectioning are very inflexible.

Drone

Might be good to add: jira.mongodb.org/browse/SERVER-3071 the more publicity it gets the more likely mongodb inc are probably gonna implement it – Guanase

Good point! I added the link to the answer because many people probably don't know the term, even if they know the feature. – Drone

Answer 2

If you use below index scan and order will be false.

db.collec.ensureIndex({a:1,c:-1,b:1});

Check this out

Extender

yes, i accept that. but the index is not exclusively for this query, its been there already and I am doing this query for first time. FYI, changing the index affects the query already depending on the index. My question is why it cannot use the index to sort, even if c is at the tail of the index? – Ragouzis