Which of the following queries will use the index?

Question

Given collection foo with the following index:

db.foo.createIndex( { a : 1, b : 1, c : 1 } )

We need to select which of the following queries will use the index?

1. db.foo.find( { c : 1 } ).sort( { a : -1, b : 1 } )
2. db.foo.find( { b : 3, c : 4 } )
3. db.foo.find( { a : 3 } )
4. db.foo.find( { c : 1 } ).sort( { a : 1, b : 1 } )

I'm surprised that 3, 4 are correct options and 1, 2 are not.

Why is the following queries will use the index?

db.foo.find( { a : 3 } )

This seems to be able to use the index however _id is not projected out.

db.foo.find( { c : 1 } ).sort( { a : 1, b : 1 } )

We're looking for c which is on the right side of the index

Why is the following queries will "not" use the index?

db.foo.find( { c : 1 } ).sort( { a : -1, b : 1 } )

db.foo.find( { b : 3, c : 4 } )

Answer 1

Query 1

db.foo.find( { c: 1 } ).sort( { a: -1, b: 1 } );

Filtering with { c: 1 } will not use the index, since { c: 1 } is not the prefix of { a: 1, b: 1, c: 1 } (documentation). Sorting with { a: -1, b: 1 } will not use the index, since sort direction (1 or -1) does matter (documentation).

Query 2

db.foo.find( { b: 3, c: 4 } );

Filtering with { b: 3, c: 4 } will not use the index, since { b: 1, c: 1 } is not the prefix of { a: 1, b: 1, c: 1 }.

Query 3

db.foo.find( { a: 3 } );

Filtering with { a: 3 } will use the index, since { a: 1 } is the prefix of { a: 1, b: 1, c: 1 }.

This seems to be able to use the index however _id is not projected out.

Field _id will be in the result of the query, unless you specify the contrary.

For document { _id: 1, a: 3, b: 4, c: 5 } consider the next queries and their results

> db.foo.find( { a: 3 }, { _id: 0, a: 1, b: 1, c: 1 } );
{ "a": 3, "b": 4, "c": 5 }
> db.foo.find( { a: 3 }, { a: 1 } ).explain("executionStats");
...
"executionStats": {
    "totalKeysExamined" : 1,
    "totalDocsExamined" : 0,
    ...
> db.foo.find( { a: 3 } );
{ "_id": 0, "a": 3, "b": 4, "c": 5 }
> db.foo.find( { a: 3 } ).explain("executionStats");
...
"executionStats": {
    "totalKeysExamined": 1,
    "totalDocsExamined": 1,
    ...

Note that in second case document's _id was fetched from the collection ("totalDocsExamined": 1) after the index scan, since the query does not say that _id field is not required in the result.

Query 4

db.foo.find( { c: 1 } ).sort( { a: 1, b: 1 } );

Sorting with { a: 1, b: 1 } will use the index, since { a: 1, b: 1 } is prefix subset of { a: 1, b: 1, c: 1 } (for non-prefix subset - see documentation) and sort keys are listed in the same order, direction as in the { a: 1, b: 1, c: 1 } (documentation). Though, the index will not be used for filtering, since { c: 1 } is not the prefix of { a: 1, b: 1, c: 1 }. Consider the next queries and their results

> db.foo.find( { c: 1 }, { _id: 0 } ).sort( { a: 1, b: 1 } ).explain("executionStats");
...
"executionStages": {
    "stage": "FETCH",
    "filter": {
        "c": { "$eq": 3 } // filtering is done by fetching from the collection
    },
    "inputStage": {
        "stage": "IXSCAN",
        "indexBounds": {
            "a": [ "[MINKEY, MAXKEY]" ],
            "b": [ "[MINKEY, MAXKEY]" ],
            "c": [ "[MINKEY, MAXKEY]" ] // index was not used for filtering c
        }
        ...
> db.foo.find( { a: 1 }, { _id: 0 } ).sort( { a: 1, b: 1 } ).explain("executionStats");
...
"executionStages": {
    "stage": "PROJECTION",
    "inputStage": {
        "stage": "IXSCAN",
        "indexBounds": {
            "a": [ "[3.0, 3.0]" ],      // index was used for filtering a
            "b": [ "[MINKEY, MAXKEY]" ],
            "c": [ "[MINKEY, MAXKEY]" ]
        }
        ...

Note that in second case both filtering and sorting were done using the index and there were no call to the collection.