In MongoDB when we are creating a compound index, the keys order defining the index matters.
These 2 compund indexes below are different, resulting in different performances when querying
db.users.createIndex({"age": 1, "name": 1, "user_id":1})
db.users.createIndex({"name": 1, "user_id": 1, "age": 1})
What is the correct criteria to create a best performing compound index? It depends on the query we are perfoming. Let’s take this query as example
db.users.find({"user_id": N, "age": {$gte: M}}).sort({"name": 1})
The index candidates fields are obviously user_id and age. name is a candidate too, because if it’s not part of the index the sorting will be made in memory resulting in worst performances (I am not going into detail in this, just take it as true or make you own research on this specific topic 🙂 ).
Now we have our candidates. What index should go first?
- Keys for equality (example shown) should go first
db.users.find({"user_id": N})
- keys for multivalue (example shown) should go last
db.users.find({"age": {$gte: M}})
- Keys for sorting should go before keys for multivalues
db.users.find().sort({"name": 1})
So the best performing index would be
db.users.createIndex({"user_id":1, "name": 1, "age": 1 })
Source: MongoDb the definitive guide, powerful and scalable data storage, by Shannon Bradsaw & Kristina Chodorow [NOT SPONSORED AMAZON LINK]
Motorcycle rider
American football player
DIY enthusiast
Web developer on free time
