Mongo compound indexes, using less-than-all in a query

Question

I understand that with MongoDB, for a query to make use of a compound index it must use ALL of the keys in the index, or at least some of the keys starting from the left. For example

db.products.find({ "a":"foo", "b":"bar" })

Will happily make use of an index made up of {a, b, c}.

However, if I want to query:

db.products.find( {"a":"foo", "c":"thing" })

I believe this can't use the index. Could this be solved by adding a trivial condition on "b", e.g.

db.products.find( {"a":"foo", "b":{ $ne : "" }, "c":"thing" })

Even when I don't actually care about the value of b. The reason for this is that we currently have 45m objects, and it's going to continue growing so we're looking to consolidate our indexes to save on resources.

Many thanks.

Answer 1

In general, a query on a multi-column index that does not sufficiently limit matches for one of the columns will restrict the usefulness of the multi-column index. In your example, using query criteria of {"a":"foo", "b":{$ne:""}, "c":"thing"} will limit the usefulness of an {a,b,c} index to matching only on a. If your query criteria will be executed often, create an {a,c,b} index (or {a,c} if b will not be used in query criteria).

Use the explain function on your queries to see if an index is being used to its full potential. If explain tells you indexOnly is true, then your query is only using the index to find matching documents; otherwise, MongoDB needs to look at each document to find matches.

For further information, see:

• Optimization
• Indexes
• Query Optimizer