Mongo compound indexes, using less

 

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", eg 

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.

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

    • 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

    链接地址: http://www.djcxy.com/p/9928.html

    上一篇: 我可以从System.Net.Security.SslStream期望安全吗？

    下一篇: Mongo复合指数使用较少