How to efficiently add a compound index with the _id field in MongoDB

Question

I am doing a range query on _id and need to return only one particular field ("data") from the found documents. I would like to make this query indexOnly for optimal performance.

Here is the query:

db.collection.find({_id:{$gte:"c",$lte:"d"}},{_id:0,data:1})

This query is of course not indexOnly so I need to add another index:

db.collection.ensureIndex({_id:1,data:1})

and tell MongoDB to use that Index with:

db.collection.find({_id:{$gte:"c",$lte:"d"}},{_id:0,data:1}).hint({_id:1,data:1})

(The hint is needed because otherwise MongoDB will use the standard _id index for the query.)

This works as expected and makes the query indexOnly. However one cannot delete the standard _id index even though it is no longer needed which leads to a lot of wasted space for the doubled index. It is also annoying to be forced to always use the hint() in the query.

So I am wondering if there is a smarter way to do this.

Answer 1

I don't believe that there is any way to do what you want. The _id index cannot be removed, and you need to have the second index in order to perform a covered (indexOnly) query on your data.

Do you really have the need to have only a single index? I would suspect that you probably only have the requirement for either increased speed or reduced disk usage, but not both. If you do really have a requirement for both increased speed and reduced disk usage, you may need to look for a different database solution, since all of the techniques used to speed up MongoDB queries (indexes, covered queries, sharding, etc) tend to trade increased disk usage in order to gain the speed boost they provide.

EDIT:

Also, if the call to hint is bugging you, you can probably leave it off since MongoDB will eventually re-optimize it's query plan at which point it will switch over to your new index if it really is faster.