Q:

Mongodb multi index search reduced performance

I have a question about the reduced performance of an index query.  I have a collection indexed by two fields ‘COLOR’ and ‘FLAVOR’.  The collection is rather hefty, weighing in at 27M documents.  When I query the collection using each field separately I get blazing faster returns
collection.find({'COLOR': 'BLUE'}).count()

collection.find({'FLAVOR': 'SWEET'}).count()

When I combine these fields into a single query I get a huge degradation in performance.  Essentially the query runs indefinitely (w/o error).
collection.find({'COLOR': 'BLUE’},{'FLAVOR': 'SWEET'}).count()

Any ideas why this is happening?

A:

A large difference in performance is likely due to needing to load documents from disk into memory. Assuming that your combined query is written incorrectly, and you meant for the query to be equivalent to
db.collection.count({ "COLOR" : "BLUE", "FLAVOR" : "SWEET" })

i.e. count the number of documents where COLOR is "BLUE" and FLAVOR is "SWEET", MongoDB will choose one of the indexes on COLOR and FLAVOR to help fulfill the query. The choice will be made based on comparing the performance of the two empirically; it doesn't matter which one is chosen for this answer, so we will assume the query uses the index { "COLOR" : 1 } and compare with
db.collection.count({ "COLOR" : "BLUE" })

The latter query just needs to look at the number of document pointers in the index BTree node for "COLOR" : "BLUE". The former query will use the BTree to find the documents where "COLOR" : "BLUE", but then must load each document and check if "FLAVOR" : "SWEET". The index on FLAVOR can't be used for the latter operation because it maps from FLAVOR values to documents, not the other way around.
You can't see this happening directly because .count returns an integer, but it's almost the same thing going on when you compare the covered query
db.collection.find({ "COLOR" : "BLUE" }, { "_id" : 0, "COLOR" : 1 })

with the query
db.collection.find({ "COLOR" : "BLUE", "FLAVOR" : "SWEET" }, { "_id" : 0, "COLOR" : 1 })

I'd suggest trying both of those queries with .explain and comparing n, nscanned, and nscannedObjects.
You can produce the count db.collection.count({ "COLOR" : "BLUE", "FLAVOR" : "SWEET" }) just using a compound index on { "COLOR" : 1, "FLAVOR" : 1 }, as mentioned in the comments.

