MongoDB Indexing and Projection

Question

I have a few questions about MongoDB:

(1) Does indexing help with projection?

(2) I have assigned a collection a number of indexes and tried to run a find with sort, and then use explain, it shows BtreeCursor index on the sorted field.

Could it be that the other indexes have helped in the query part and explain just didn't show it because it shows only the last index that helped the find?

Or explain should show all indexes that assisted in querying, sorting, etc?

Thanks.

Answer 1

Does indexing helps in projection?

I believe the only time it will really help (defined by performance etc) is if the query is "covered": http://docs.mongodb.org/manual/tutorial/create-indexes-to-support-queries/

So for example, if you wanted to query on {d:1, e:2} and get back {_id, t, e}, you would do:

db.t.ensureIndex({d:1 , e:1, _id:1, t:1});
db.t.find({d:1, e:2}, {_id:1, t:1, e:1});

And that query's explain() output would show indexOnly as true meaning that it never loaded documents from disk to return a response.

So yes, indexes can help with projection under certain circumstances.

I have assigned a collection a number of indexes and tried to run a find with sort, and then use explain, it shows BtreeCursor index on the sorted field.

Yes it does.

Could it be that the other indexes have helped in the query part and explain just didn't show it because it shows only the last index that helped the find?

If you are a victim of index intersectioning then you would use an explain(true) to show all index plans that were used.

It is good to note that separate indexes are not used for find and sort with intersectioning, so the answer here is actually no: http://docs.mongodb.org/manual/core/index-intersection/#index-intersection-and-sort