[DOCS-14794] [SERVER] improvement required for the " $exists " documentation Created: 14/Sep/21  Updated: 30/Oct/23  Resolved: 09/Nov/21

Status: Closed
Project: Documentation
Component/s: manual, Server
Affects Version/s: None
Fix Version/s: Server_Docs_20231030

Type: Task Priority: Major - P3
Reporter: Dror Mikdash (Inactive) Assignee: Jason Price
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Participants:
Days since reply: 2 years, 13 weeks, 1 day ago
Epic Link: DOCSP-11702
Story Points: 3

 Description   

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



 Comments   
Comment by Githook User [ 09/Nov/21 ]

Author:

{'name': 'jason-price-mongodb', 'email': '69260375+jason-price-mongodb@users.noreply.github.com', 'username': 'jason-price-mongodb'}

Message: DOCS-14794 exists update (#6100) (#6104)

Co-authored-by: jason-price-mongodb <jshfjghsdfgjsdjh@aolsdjfhkjsdhfkjsdf.com>

Co-authored-by: jason-price-mongodb <jshfjghsdfgjsdjh@aolsdjfhkjsdhfkjsdf.com>
Branch: v4.0
https://github.com/mongodb/docs/commit/5ef89d96d9ab49cd938b281f2d8273ae977a3b86

Comment by Githook User [ 09/Nov/21 ]

Author:

{'name': 'jason-price-mongodb', 'email': '69260375+jason-price-mongodb@users.noreply.github.com', 'username': 'jason-price-mongodb'}

Message: DOCS-14794 exists update (#6100) (#6103)

Co-authored-by: jason-price-mongodb <jshfjghsdfgjsdjh@aolsdjfhkjsdhfkjsdf.com>

Co-authored-by: jason-price-mongodb <jshfjghsdfgjsdjh@aolsdjfhkjsdhfkjsdf.com>
Branch: v4.2
https://github.com/mongodb/docs/commit/f36c720d01e4c1e1b5c7d58de70f43e7a30fc7d7

Comment by Githook User [ 09/Nov/21 ]

Author:

{'name': 'jason-price-mongodb', 'email': '69260375+jason-price-mongodb@users.noreply.github.com', 'username': 'jason-price-mongodb'}

Message: DOCS-14794 exists update (#6100) (#6102)

Co-authored-by: jason-price-mongodb <jshfjghsdfgjsdjh@aolsdjfhkjsdhfkjsdf.com>

Co-authored-by: jason-price-mongodb <jshfjghsdfgjsdjh@aolsdjfhkjsdhfkjsdf.com>
Branch: v4.4
https://github.com/mongodb/docs/commit/659b57e7495bb7951c55f1bdadef7dc0e662aebc

Comment by Githook User [ 09/Nov/21 ]

Author:

{'name': 'jason-price-mongodb', 'email': '69260375+jason-price-mongodb@users.noreply.github.com', 'username': 'jason-price-mongodb'}

Message: DOCS-14794 exists update (#6100) (#6101)

Co-authored-by: jason-price-mongodb <jshfjghsdfgjsdjh@aolsdjfhkjsdhfkjsdf.com>

Co-authored-by: jason-price-mongodb <jshfjghsdfgjsdjh@aolsdjfhkjsdhfkjsdf.com>
Branch: v5.0
https://github.com/mongodb/docs/commit/ac5240246514a86aeca2cf5c72795447db32091c

Comment by Githook User [ 09/Nov/21 ]

Author:

{'name': 'jason-price-mongodb', 'email': '69260375+jason-price-mongodb@users.noreply.github.com', 'username': 'jason-price-mongodb'}

Message: DOCS-14794 exists update (#6100)

Co-authored-by: jason-price-mongodb <jshfjghsdfgjsdjh@aolsdjfhkjsdhfkjsdf.com>
Branch: master
https://github.com/mongodb/docs/commit/9c6daabed7454953653161ccb596ffc75e2786fe

Comment by Dror Mikdash (Inactive) [ 09/Nov/21 ]

jason.price  saw the changes.  lgtm

Comment by Dave Cuthbert (Inactive) [ 14/Sep/21 ]

dror.mikdash Is this new in version 5.0 or does the behavior also show up in earlier MDB versions?

Generated at Thu Feb 08 08:11:12 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.