-
Type: Task
-
Resolution: Done
-
Priority: Major - P3
-
Affects Version/s: None
-
Labels:None
The following information/clarification should be added to $exists documentation, to greatly reduce repetitive questions support is getting about the performance of exists :
add a new section to the $exists page-
Improving $EXISTS performance
when running a query for retrieve/count documents where
field:{ $exists:true}
even though the field we are querying by is indexed , the all the collection pages will be examined , instead of just scanning index
This is highly inefficient and significantly degrades query performance
For example :
db.collection.countDocuments({auditDate:{$exists:true}})
(query meaning - search for documents where the auditDate field exists in a collection (either with a value or with a null value)
To make such query more efficient - a sparse index should be created over the specific field.
example for creating a sparse index:
> db.getCollection('collection').createIndex( { 'auditDate': 1 }, { name: 'auditDate_sparse_index_1', sparse: true } )
When the sparse index is in place, such a query will run exclusively using the index with increased efficiency and speed.
an alternative query to using $exists:
(Relevant for use cases where a user is looking only for documents where the field we are querying by is not-null)
using a standard index (no need for sparse index) , run:
db.collection.countDocuments({auditDate:{$ne:null}})