Uploaded image for project: 'Documentation'
  1. Documentation
  2. DOCS-14794

[SERVER] improvement required for the " $exists " documentation

      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}})

            Assignee:
            jason.price@mongodb.com Jason Price
            Reporter:
            dror.mikdash@mongodb.com Dror Mikdash (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            7 Start watching this issue

              Created:
              Updated:
              Resolved:
              2 years, 24 weeks, 2 days ago