Can MongoDB use an index when checking for existence of a field with $exists operator?

Question

If I have data in my users collection that looks like:

{ name: '...', 
  email: '...', 
  ...,
  photos: {
     123: { url: '...', title: '...', ... },
     456: { url: '...', title: '...', ... },
     ...
  }
}

And I want to find which user owns photo id 127, then I am using the query:

db.users.find( {'photos.127': {'$exists' => true} } );

I've tried, but it doesn't seem possible to get MongoDB to use an index for this query. The index I tried was: db.users.ensureIndex({photos:1});. And when I used explain() mongo told me it was using a BasicCursor (i.e., no index was used).

Is it possible to create an index that mongo will use for this query?

RameshVel · Accepted Answer · 2020-10-10T16:01:25.523

16

Updated:

Seems $exists queries use index properly now based on these tickets $exists queries should use index & {$exists: false} will not use index

Old Answer:

No, there is no way to tell mongodb to use index for exists query. Indexing is completely related to data. Since $exists is only related to the keys (fields) it cant be used in indexes.

$exists just verifies whether the given key (or field) exists in the document.

edited Oct 10 '20 at 16:01

answered Nov 18 '11 at 06:16

RameshVel

62,788
28
167
209

12

NO!!! I tested with mongodb 2.4.3, and it USES the index. Note that nonexistent fields, have value Null in index. – Taha Jahangir May 10 '13 at 14:02
4

MongoDB 3.0.3: `explain` for both `find({field: {$exists: 1}})` and `find({field: {$ne: null}})` looks like it's going to use index, but whereas the second query finishes instantly, the first one stucks (w/ full scan, I guess). – vorou Jun 17 '15 at 05:54
Well that sucks. Keys are data and should be able to be indexed. Allowing objects as data kinda sucks if you can't index them.. – B T Jul 22 '17 at 21:49
4

`$exists` uses index now (tested on mongo 3.2) – Sergio Tulentsev Dec 27 '18 at 18:48

score 7 · Answer 2 · answered Dec 03 '13 at 09:50

7

Since MongoDB 2.0 $exists queries should use an index. Unfortunately this fix has disappeared in the newest version and will be fixed in MongoDB 2.5

answered Dec 03 '13 at 09:50

H6.

29,382
12
75
80

jianpx · Answer 3 · 2013-02-02T09:13:03.897

$exist will not use index, but you can change your data structure to

photos: [
     {id:123, url: '...', title: '...', ... },
     {id:456, url: '...', title: '...', ... },
     ...
  ]

and then use

db.users.ensureIndex({photos.id:1})

to create index for photo id.

It seems I am wrong, in fact, you can force your $exists query to use your index. Let us go on using the above structure, but your photo id is not certainly contained , that is to say some docs will have the key 'id' and some will not. Then you can create sparse index on it:

db.users.ensureIndex({'photos.id': 1}, {'sparse': true})

then query like this:

db.users.find({'photos.id': {$exists: true}}).hint({'photos.id': 1})

you can add explain to see if the query is using index. Here is my result, my collection's mobile key is similar to your photos.id:

> db.test.count()
50000
> db.test.find({'mobile': {$exists: true}}).hint({'mobile': 1}).explain()
{
        "cursor" : "BtreeCursor mobile_1",
        "nscanned" : 49999,
        "nscannedObjects" : 49999,
        "n" : 49999,
        "millis" : 138,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "isMultiKey" : false,
        "indexOnly" : false,
        "indexBounds" : {
                "mobile" : [
                        [
                                {
                                        "$minElement" : 1
                                },
                                {
                                        "$maxElement" : 1
                                }
                        ]
                ]
        }
}

> db.test.getIndexes()
[
        {
                "v" : 1,
                "key" : {
                        "_id" : 1
                },
                "ns" : "test.test",
                "name" : "_id_"
        },
        {
                "v" : 1,
                "key" : {
                        "mobile" : 1
                },
                "ns" : "test.test",
                "name" : "mobile_1",
                "sparse" : true,
                "background" : true
        }
]

Hope to help!

`$exists` uses index now (tested on mongo 3.2) – Sergio Tulentsev Dec 27 '18 at 18:48 — Sergio Tulentsev, Dec 27 '18 at 18:48

Can MongoDB use an index when checking for existence of a field with $exists operator?

3 Answers3

Linked

Related