8

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.

Chris
  • 2,984
  • 25
  • 24
  • Can you give explain of these queries? – Alexey Churkin Nov 10 '11 at 08:02
  • Your understanding is strictly true on a double compound index, but not necessarily true in a triple compound index. Using explain you will see that the a,b,c index will in fact be used when you query on a,c. – Zaid Masud Aug 02 '12 at 12:16

2 Answers2

3

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:

Go Dan
  • 15,194
  • 6
  • 41
  • 65
0

In fact, using newer Mongo versions, your {a,b,c} index will be used when there is a query on {a,c} with no trivial condition on b necessary. I have verified this using the explain command.

There is a detailed discussion of this here: Mongo Triple Compound Index

Community
  • 1
  • 1
Zaid Masud
  • 13,225
  • 9
  • 67
  • 88