0

In my replica set mongodb cluster, a read query could occur:

- either on column1
- or on column2
- or on combination of column1 and column2
- or on combination of column1 and column3
- or on combination of column2 and column3
- or on combination of column1 and column2 and column3

Would it make sense to create 6 indexes for each case above or there is an efficient way to club these under lesser number of indexes?

earl
  • 738
  • 1
  • 17
  • 38
  • Creating so many indexes can affect the write performance on your server. You can take advantage of [Compound Index - Prefixes](https://docs.mongodb.com/v4.2/core/index-compound/index.html#prefixes) to design your index and the queries. – prasad_ Aug 31 '20 at 07:43
  • The Compound Index works as 'and' or 'or' ? I think if I create a compound index with column1 and column3, then query on only column1 might not be able to leverage this index. – earl Aug 31 '20 at 07:58
  • There can be more than one compound index. Also, see the topics in [Indexing Strategies](https://docs.mongodb.com/v4.2/applications/indexes/), like Create Queries that Ensure Selectivity. You may have to make some compromises and work with the design. It will be a little bit of "trial and error" process. – prasad_ Aug 31 '20 at 08:11
  • https://stackoverflow.com/questions/62263023/what-is-the-correct-way-to-index-in-mongodb-when-big-combination-of-fields-exist/62268015#62268015 – D. SM Aug 31 '20 at 14:47

2 Answers2

1

An index on {column1: 1, column2: 1, column 3:1 } can service queries on:

  • column1
  • column1 and column2
  • column1 and column2 and column3

An index on {column2: 1, column3: 1} can service queries on:

  • column2
  • column2 and column3

An index on {column3: 1, column1: 1} can service queryies on:

  • column3
  • column3 and column1

All of your queries noted could be handled properly with 3 compound indexes.

If you do find it necessary to create lots of indexes, note that mongod has a limit of 64 indexes per collection.

Joe
  • 25,000
  • 3
  • 22
  • 44
1

Create three indexes, one each for every field. Mongo is able to combine several indexes, see Index Intersection

Verify performance and have a look at execution plans (using explain())

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110