CODEWITHSUNDEEP
mongodb
jackalope
jackalope

Reputation: 1564

How to do efficent query to replace $exists in MongoDB

I have a MongoDB collection with various data in it. (about millions)

These data have a data struct like {k: {a:1,b:2,c:{},...}} and I don't know extactly what in it.

Now I wanna do a counting on this collection to return me the total elements in the collection that k is not empty by using {k:{$exists:true}} but that's turns out very slow ...

Then I add an index on k and trying to query by : {k:{$gt:{}} but that's not return the correct results.

So, how to do this counting on the collection now?

Note that I don't know the data structure of k.

Upvotes: 1

Views: 487

Answers (3)

Alex
Alex

Reputation: 38529

If you are using a version before version 2, $exists is not able to use an index. See this answer: https://stackoverflow.com/a/7503114/131809

So, try upgrading your version of MongoDB

From the docs:

Before v2.0, $exists is not able to use an index. Indexes on other fields are still used.
$exists is not very efficient even with an index, and esp. with {$exists:true} since it will effectively have to scan all indexed values.

The second part of that is perhaps the important bit.

It sounds like sparse index may be the key here...

Upvotes: 1

Sushant Gupta
Sushant Gupta

Reputation: 9458

db.collection.count({k:{$ne:null}})

By the way use sparse index on k.

db.collection.ensureIndex({k:1}, {sparse: true});

Upvotes: 0

Alex
Alex

Reputation: 38529

Try using $ne : null

So, as per your code example:

{k:{$ne : null}}

Upvotes: 0

Related Questions