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

Question

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?

It has to be part of a compound index. Does this answer your question? stackoverflow.com/questions/36142299/… — kevinadi
– kevinadi, Commented Aug 27, 2019 at 1:52

Dexter · Accepted Answer · 2019-08-27 04:06:56Z

1

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

answered Aug 27, 2019 at 4:06

Dexter

9928 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related