Geek Answers Handbook

Can MongoDB use an index when checking for a field with the $ exist operator?

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

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

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

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

I tried, but it is not possible to force MongoDB to use the index for this query. The index I tried was: db.users.ensureIndex({photos:1}); . And when I used explain() , the mongo told me that it was using BasicCursor (i.e. no index was used).

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

+7
source share
3 answers

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

$ exists only checks if the given document (or field) exists in the document.

+12
source

$ exist will not use an 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 an index for the photo id.

I seem to be mistaken, in fact you can force your $ exist query to use your index. Let's continue to use the above structure, but your photo identifier is not necessarily contained, that is, some documents will have an "id" key, and some will not. Then you can create a sparse index on it:

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

then run the query as follows:

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

you can add an explanation to see if the query uses an index. Here is my result, my mobile key for mobile devices 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!

+6
source

Since MongoDB 2.0 $exists queries must use an index. Unfortunately, this fix disappeared in the latest version and will be fixed in MongoDB 2.5

+3
source

More articles:


All Articles