3

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?

vivek_jonam
  • 3,237
  • 8
  • 32
  • 44

2 Answers2

5

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.

mnemosyn
  • 45,391
  • 6
  • 76
  • 82
  • 1
    Might be good to add: https://jira.mongodb.org/browse/SERVER-3071 the more publicity it gets the more likely mongodb inc are probably gonna implement it – Sammaye Oct 22 '13 at 12:47
  • Good point! I added the link to the answer because many people probably don't know the term, even if they know the feature. – mnemosyn Oct 22 '13 at 12:55
0

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

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

Check this out

titogeo
  • 2,156
  • 2
  • 24
  • 41
  • 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? – vivek_jonam Oct 22 '13 at 11:34