Is there a good way to get distinct values in large collections in Mongo?

Question

I try to get all the distinct value (less than 10 possible values) of a given field in a large collection (3.500.000 docs, 35gb).

I tried to get these values with a db.collection.distinct('field'), but it is very slow, even if there is an index (it doesn't seems to be used).

Any suggestions to improve performance on this query?

Thanks

Edit I was using Mongo 2.4.9. It has been fixed in 2.5.5 (https://jira.mongodb.org/browse/SERVER-2094), but I still have a performance issue on queries like this db.logs.distinct( "version", {wsId: "XXX" }) even if indexes exists for both fields.

Answer 1

The question is a bit old, but I faced a similar issue now and ended up with the solution of caching distinct values (since in my case they are rarely changed)

Might be helpful for someone.

import datetime
import pickle
import redis    

def get_distinct_values(collection, column_name):
    redis = Redis(settings.REDIS_SERVER, settings.REDIS_PORT)
    key = "distinct_{}".format(column_name)
    if not redis.exists(key):
        res = list(collection.distinct(column_name))
        redis.set(key, pickle.dumps(res))
        redis.expire(key, datetime.timedelta(days=1))
    else:
        res = pickle.loads(redis.get(key))
    return res

Answer 2

"distinct" makes use of the the index if its available. Run it like this and see if index is being used:

db.runCommand({distinct: "collectionNameGoesHere", key:"fieldNameGoesHere"})

the last value in the returned result set is stats that looks like this:

   "stats" : {
           "n" : 280,
           "nscanned" : 280,
           "nscannedObjects" : 0,
           "timems" : 0,
           "cursor" : "BtreeCursor class_id_1"
   }

Notice that my query had used an index on the class_id field since I had pre-made it.