I have an index on multiple columns - the last of which is a sort. The penultimate column in the index has very low cardinality - say 3,000 out of 5m records, is unlikely to go above 5,000 or so. When running an explain, the index is used, but mongo is doing a scan on 240k records to find out that only 343 of them have @fields.current.value : 1, despite that being in the index. If I remove that column from the index, I get exactly the same results as if it's there. However removing the column from the query it runs about three times faster. To me it seems like that part of the index ought to be used.

Explain:

> db.fields_current.node.find({ 'status': 1, '@fields': { '$elemMatch':

{ 'ex_node_group.value': 1, 'edition.tid': 369, 'channel.tid': 1, 'current.value': 1 }

} }).sort(

{ '@fields.pageviews.value': -1 }

).hint('content_list_homepage_popular_index').explain();
{
"cursor" : "BtreeCursor content_list_homepage_popular_index",
"indexBounds" : [
[
{
"@fields.edition.tid" : 369,
"@fields.ex_node_group.value" : 1,
"status" : 1,
"@fields.current.value" : 1,
"@fields.pageviews.value" :

{ "$maxElement" : 1 }

},
{
"@fields.edition.tid" : 369,
"@fields.ex_node_group.value" : 1,
"status" : 1,
"@fields.current.value" : 1,
"@fields.pageviews.value" :

{ "$minElement" : 1 }

}
]
],
"nscanned" : 240011,
"nscannedObjects" : 240011,
"n" : 343,
"millis" : 1474
}
> db.fields_current.node.find({ 'status': 1, '@fields': { '$elemMatch':

{ 'ex_node_group.value': 1, 'edition.tid': 369, 'channel.tid': 1, 'current.value': 1 }

} }).count();
343
> db.fields_current.node.find({ 'status': 1, '@fields': { '$elemMatch':

{ 'ex_node_group.value': 1, 'edition.tid': 369, 'channel.tid': 1}

} }).count();
64545
>