31

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?

bantic
  • 4,886
  • 4
  • 29
  • 34

5 Answers5

18

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.

RameshVel
  • 64,778
  • 30
  • 169
  • 213
  • 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
  • 5
    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
  • Will it be used in agregation too? I have issues with a pipeline like so: `"$match": { "foo": "someValue", "bar": { "$exists": true } } ` it seems to respect the index on "foo" but not on "bar". I've a compound index on foo and bar + an index on bar. – Eric Burel Dec 12 '22 at 09:21
  • https://jira.mongodb.org/browse/SERVER-12869 is still open. This has not been properly resolved, since it will only use the index in certain cases and/or give you incorrect results. – Matthew Read Feb 16 '23 at 22:27
6

$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!

jianpx
  • 3,190
  • 1
  • 30
  • 26
6

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

disco crazy
  • 31,313
  • 12
  • 80
  • 83
6

As of June 2022 the index CANNOT be used for ${exists: true} case and CAN BE ONLY PARTIALLY used for the half of other cases. There is a major (IMO) design bug with the MongoDB index which people are still often unaware of so I'm posting this answer with a workaround for some cases.

The issue is that MongoDB indexes non-existing fields as nulls and as such these fields are indistinguishable from nulls from the index perspective. In addition there is some mess related to JS analogy, where undefined === null is false but undefined == null is true. That means:

  • When searching for {$exists: false} the index can be used. For that both null and non-existing values are scanned in the index , documents are fetched, and values equal to null are filtered out. Corresponding MongoDB stages: [null, null] IXSCAN and FETCH with {"$not" : {$exists: true}} filter.
  • When searching for {field: null} the index can be used. For that, just the index scan is needed. MongoDB parses that as {field: { $eq: null }}. For some likely historical reasons Mongo also searches for deprecated undefined type/value: [undefined, undefined]U[null, null] IXSCAN. IMO it would be way more logical to just interpret equality as value equality and not include non-existing fields in the results. But it is what it is.
  • To REALLY search for value equal to null, we need to search for field: {$type: "null"}. Index can be used. Similarly to the first case, stages are: [null, null] IXSCAN and FETCH with {'$type': [10]} filter. Values with non-existing field are unnecessarily fetched and then filtered out.
  • If you need to retrieve all existing values, i.e. including those with value null, then you are in bad luck. The search query is {field: {$exists: true}}. Index cannot be used. If mongo was using the index, it would include values indexed as null to include documents with the field equal to null, to later filter out non-existing values. So full index would be needed, and so the full collection scan is more efficient. Mongo stages: COLLSCAN with { '$exists': true } filter.
  • If you can live without values equal to null, i.e. it is fine to not include $type: null in results, then index can be used. If you know your field type is e.g. ObjectId then you can search for {field: {$type: "object"}}, for the list of types: {field: {$type: ["number", "object"]}}, or just {field: {$ne: "null"}}. The latter will exclude null and undefined types from the search. Mongo stages are:
    • [{},[]) IXSCAN and FETCH
    • '[nan.0, inf.0]U[{}, [])' and FETCH
    • [MinKey, undefined)U(null, MaxKey] IXSCAN and FETCH.

There is an issue filed on Feb 2014: https://jira.mongodb.org/browse/SERVER-12869. Unfortunately, MongoDB hasn't prioritized it yet, nor reflected the issue in the official documentation.

Sergei Kuzmin
  • 708
  • 7
  • 12
1

Posting it here with latest changes as of February 2023 - if you wish to use $exists with an index, better consult the table here: https://www.mongodb.com/docs/manual/reference/operator/query/exists/

If you wish to use an index on a query of { $exists: true }, best approach would be to use sparse index

Lior Gross
  • 319
  • 2
  • 4