Index Intersection

MongoDB can use the intersection of multiple indexes to fulfillqueries. In general, each index intersectioninvolves two indexes; however, MongoDB can employ multiple/nested indexintersections to resolve a query.

To illustrate index intersection, consider a collection orders thathas the following indexes:

  1. { qty: 1 }
  2. { item: 1 }

MongoDB can use the intersection of the two indexes to supportthe following query:

  1. db.orders.find( { item: "abc123", qty: { $gt: 15 } } )

To determine if MongoDB used index intersection, runexplain(); the results of explain() will include either anAND_SORTED stage or an AND_HASH stage.

Index Prefix Intersection

With index intersection, MongoDB can use an intersection of either theentire index or the index prefix. An index prefix is a subset of acompound index, consisting of one or more keys starting from thebeginning of the index.

Consider a collection orders with the following indexes:

  1. { qty: 1 }
  2. { status: 1, ord_date: -1 }

To fulfill the following query which specifies a condition on both theqty field and the status field, MongoDB can use theintersection of the two indexes:

  1. db.orders.find( { qty: { $gt: 10 } , status: "A" } )

Index Intersection and Compound Indexes

Index intersection does not eliminate the need for creatingcompound indexes. However, because boththe list order (i.e. the order in which the keys are listed in theindex) and the sort order (i.e. ascending or descending), matter incompound indexes, a compound index maynot support a query condition that does not include the indexprefix keys or that specifies a different sortorder.

For example, if a collection orders has the following compoundindex, with the status field listed before the ord_date field:

  1. { status: 1, ord_date: -1 }

The compound index can support the following queries:

  1. db.orders.find( { status: { $in: ["A", "P" ] } } )
  2. db.orders.find(
  3.    {
  4.      ord_date: { $gt: new Date("2014-02-01") },
  5.      status: {$in:[ "P", "A" ] }
  6.    }
  7. )

But not the following two queries:

  1. db.orders.find( { ord_date: { $gt: new Date("2014-02-01") } } )
  2. db.orders.find( { } ).sort( { ord_date: 1 } )

However, if the collection has two separate indexes:

  1. { status: 1 }
  2. { ord_date: -1 }

The two indexes can, either individually or through index intersection,support all four aforementioned queries.

The choice between creating compound indexes that support your queriesor relying on index intersection depends on the specifics of yoursystem.

See also

compound indexes,Create Compound Indexes to Support Several Different Queries

Index Intersection and Sort

Index intersection does not apply when the sort()operation requires an index completely separate from the querypredicate.

For example, the orders collection has the following indexes:

  1. { qty: 1 }
  2. { status: 1, ord_date: -1 }
  3. { status: 1 }
  4. { ord_date: -1 }

MongoDB cannot use index intersection for the following query with sort:

  1. db.orders.find( { qty: { $gt: 10 } } ).sort( { status: 1 } )

That is, MongoDB does not use the { qty: 1 } index for the query,and the separate { status: 1 } or the { status: 1, ord_date: -1} index for the sort.

However, MongoDB can use index intersection for the following querywith sort since the index { status: 1, ord_date: -1 } can fulfillpart of the query predicate.

  1. db.orders.find( { qty: { $gt: 10 } , status: "A" } ).sort( { ord_date: -1 } )