Index intersection vs. compound index?

Question

So, I don't fully understand some distinctions between index intersection and using compound indexes.

Is it possible for mongodb to use multiple index intersections per query? What is the performance difference between compound indexes and index intersection?

Answer 1

Is it possible for mongodb to use multiple index intersections per query?

Index intersection is a new feature introduced in the MongoDB 2.5.5 development build, and will be included in the MongoDB 2.6 production release.

Index intersection involves the use of multiple indexes to satisfy a query; compound indexes are indexes on multiple fields. It is possible for index intersection to use either an entire index or an index prefix of a compound index. In general, as at MongoDB 2.5.5 each index intersection involves two indexes; however, MongoDB can employ multiple/nested index intersections to resolve a query.

What is the performance difference between compound indexes and index intersection?

This is going to vary based on your use case. Index intersection provides flexibility if there isn't a suitable compound index to cover all of your common queries, but a compound index may be more selective and be able to find results inspecting fewer index entries. Conversely, if you are able to take advantage of index intersection to replace multiple compound indexes there may be benefits of reduced overhead for index maintenance.

Your best approach is to test this in your development/staging environment.

There is an Index Intersection page in the manual which goes into some more detail on how this works.