Search code examples
mongodbindexingindices

Mongodb: Use sort key as part of compound index or in its own index?

Let's pretend you have the following collection:

{name: 'john', occupation: 'engineer', country: 'UK', age: 20},
{name: 'allan', occupation: 'teacher', country: 'US', age: 25},
...

And you need to find some persons (e.g. all drug dealers named Kevin) and sort them by their age:

collection.find({name: 'kevin', occupation: 'drug-dealer'}).sort({age: -1})

Later you might do a different query where you find all drug-dealers (no matter their name) and sort them by their age:

collection.find({occupation: 'drug-dealer'}).sort({age: -1})

Would it be better to have a separate index on 'age' or make it a part of several compound indices?

Part of compound indices:

{occupation: 1, age: 1}
{name: 1, occupation: 1, age:1}

Separate index:

{occupation: 1}
{name: 1, occupation: 1}
{age: 1}

What would yield the best performance? What are the pro's and con's of each?

Solution

  • A compound index would do. Since you're are running sort in your queries, make sure you build the compound index in the correct order so that your query can utilize the index not only to filter the documents but also to sort them. Else mongodb will perform in-memory sort which isn't efficient and is bound to limit of 32 MB