Search code examples
mongodbmongodb-queryreplicaset

Indexing in MongoDB Replica Set Cluster

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?

Solution

  • 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.