Search code examples
mongodbindexingindices

Mongodb multiple compound indices - same keys?

Consider the following compound indices:

{name: 1, occupation: 1, time: 1}

{name: 1, time: 1}

Say you have two types of queries:

{name: 'john', occupation: 'engineer'}

{name: 'allan', time: {$lt: 3}}

Will the second type of query be able to use the first index? Note that it does not specify an occupation.

And is it just as efficient as using the second index?

Solution

  • Will the second type of query be able to use the first index? Note that it does not specify an occupation.

    Yes.

    However, it'll be relatively inefficient (depending on how many occupations you have, and how many identical names you have) compared to {name: 1, time: 1}.

    Either index should be ok since name should be quite selective.

    Check out db.collection.explain() and the resulting explain results to check the query planning and which index is used by your queries.

    Note that if your collection is quite small, either index will do well. To have a more realistic simulation, insert random data using something like mgeneratejs that can generate e.g. 1 million example documents.