Do $exists and $in/nin count as equality queries for non-prefix sorts?

Question

http://docs.mongodb.org/manual/tutorial/sort-results-with-indexes/#sort-and-non-prefix-subset-of-an-index

The index

{ a: 1, b: 1, c: 1}

supports this query:

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

Does it also support

db.data.find( { b: 3, a: {$exists: true} } ).sort( { c: 1 } )
db.data.find( { b: {$in: [3,4]}, a: 4 } ).sort( { c: 1 } )
db.data.find( { b: {$nin: [3,4]}, a: {$exists: false} } ).sort( { c: 1 } )

sheilak sheilak · Accepted Answer · 2015-07-12T21:01:40

All the queries will use the index for retrieval but only the 2nd for sorting, as the others use operators that are not equality conditions.

You can prove this by running the queries with .explain()

See output of explain (from MongoDB 2.6.3) for these 3 queries below, with unnecessary fields omitted. "cursor" shows the index used and "indexBounds" shows the lower and upper index key bounds. "scanAndOrder" : false indicates that the index was also used for sorting.

"indexBounds" is important because you can see the equality conditions - for example, note that "$exists" : false IS an equality condition (to null) and would potentially use the index while "$exists" : true is a range.

Also see documentation on explain, it's really useful for understanding index usage and diagnosing performance issues.

db.data.find( { b: 3, a: {$exists: true} } ).sort( { c: 1 } ).explain()
{
    "cursor" : "BtreeCursor a_1_b_1_c_1",
    "scanAndOrder" : true,
    "indexBounds" : {
        "a" : [
            [
                {
                    "$minElement" : 1
                },
                {
                    "$maxElement" : 1
                }
            ]
        ],
        "b" : [
            [
                3,
                3
            ]
        ],
        "c" : [
            [
                {
                    "$minElement" : 1
                },
                {
                    "$maxElement" : 1
                }
            ]
        ]
    },
}

,

db.data.find( { b: {$in: [3,4]}, a: 4 } ).sort( { c: 1 } ).explain()
{
    "clauses" : [
        {
            "cursor" : "BtreeCursor a_1_b_1_c_1",
            "scanAndOrder" : false,
            "indexBounds" : {
                "a" : [
                    [
                        4,
                        4
                    ]
                ],
                "b" : [
                    [
                        3,
                        3
                    ]
                ],
                "c" : [
                    [
                        {
                            "$minElement" : 1
                        },
                        {
                            "$maxElement" : 1
                        }
                    ]
                ]
            }
        },
        {
            "cursor" : "BtreeCursor a_1_b_1_c_1",
            "scanAndOrder" : false,
            "indexBounds" : {
                "a" : [
                    [
                        4,
                        4
                    ]
                ],
                "b" : [
                    [
                        4,
                        4
                    ]
                ],
                "c" : [
                    [
                        {
                            "$minElement" : 1
                        },
                        {
                            "$maxElement" : 1
                        }
                    ]
                ]
            }
        }
    ],
}

,

db.data.find( { b: {$nin: [3,4]}, a: {$exists: false} } ).sort( { c: 1 } ).explain()
{
    "cursor" : "BtreeCursor a_1_b_1_c_1",
    "scanAndOrder" : true,        
    "indexBounds" : {
        "a" : [
            [
                null,
                null
            ]
        ],
        "b" : [
            [
                {
                    "$minElement" : 1
                },
                3
            ],
            [
                3,
                4
            ],
            [
                4,
                {
                    "$maxElement" : 1
                }
            ]
        ],
        "c" : [
            [
                {
                    "$minElement" : 1
                },
                {
                    "$maxElement" : 1
                }
            ]
        ]
    },
}

Do $exists and $in/nin count as equality queries for non-prefix sorts?

1 Answers