MongoDB understanding compound indexes

Question

I have 6 indexed fields in my collection. My app uses aggregation queries with different combinations of them. Here is some possible examples:

1. {$match: {$and: [{field1: {$in: [1,2,3]}}, {$field5: {$gt: 8}}]}}
2. {$match: {$and: [{field2: {$nin: [4,5,6]}}, {$field5: {$lte: 8}}, {$field6: 5}]}}
3. {$match: {field1: {$in: [7,8,9]}}}

As I said before, all fields are indexed. Of cource, when I'm executing first query, explain() function tells me, that only one index is being used. The number of fields in query depends on user's choices.

Should I create indexes for every combination of fields (I mean like {field1:1,field2:1}, {field1:1,field2:1,field3:1} etc) or I can just make one compound index for all the fields like {field1:1,field2:1,field3:1,field4:1,field5:1,field6:1}?

I think the first decision could be a little weird, because there is so much combinations for 6 fields.

Abhijith S Abhijith S · Accepted Answer · 2017-05-10T12:39:25

Your second way will work but change the sorting order.

Two things to note:

An index supports a query when the index contains all the fields scanned by the query. The query scans the index and not the collection. Creating indexes that support queries results in greatly increased query performance.

If you sometimes query on only one key and at other times query on that key combined with a second key, then creating a compound index is more efficient than creating a single-key index. MongoDB will use the compound index for both queries. A single compound index on multiple fields can support all the queries that search a “prefix” subset of those fields.

Example:

The following index on a collection:

{ x: 1, y: 1, z: 1 } Can support queries that the following indexes support:

{ x: 1 } { x: 1, y: 1 } There are some situations where the prefix indexes may offer better query performance: for example if z is a large array.

The { x: 1, y: 1, z: 1 } index can also support many of the same queries as the following index:

{ x: 1, z: 1 } Also, { x: 1, z: 1 } has an additional use. Given the following query:

db.collection.find( { x: 5 } ).sort( { z: 1} ) The { x: 1, z: 1 } index supports both the query and the sort operation, while the { x: 1, y: 1, z: 1 } index only supports the query. For more information on sorting, see Use Indexes to Sort Query Results.

Basic Example for sort on Multiple fields:

You can specify a sort on all the keys of the index or on a subset; however, the sort keys must be listed in the same order as they appear in the index. For example, an index key pattern { a: 1, b: 1 } can support a sort on { a: 1, b: 1 } but not on { b: 1, a: 1 }.

For a query to use a compound index for a sort, the specified sort direction for all keys in the cursor.sort() document must match the index key pattern or match the inverse of the index key pattern. For example, an index key pattern { a: 1, b: -1 } can support a sort on { a: 1, b: -1 } and { a: -1, b: 1 } but not on { a: -1, b: -1 } or {a: 1, b: 1} Read this.

MongoDB understanding compound indexes

2 Answers