Order of Fields Matters on MongoDB Indexes
Order of Fields Matters on MongoDB Indexes. Let's see how with an example.
12 April 2014
2 minutes read
Related Posts
As my MongoDB journey continues, I discover new stuff along the way and one of them is about indexes in MongoDB. Let me try to explain it with a sample.
First, create the below four documents inside our users collection:
db.users.save({
_id: 1,
name: "tugberk1",
login: [
{ProviderName: "twitter", ProviderKey: "232"},
{ProviderName: "facebook", ProviderKey: "423"}
]
});
db.users.save({
_id: 2,
name: "tugberk23",
login: [
{ProviderName: "twitter", ProviderKey: "3443"}
]
});
db.users.save({
_id: 3,
name: "tugberk4343",
login: [
{ProviderName: "dropbox", ProviderKey: "445345"}
]
});
db.users.save({
_id: 4,
name: "tugberk98",
login: [
{ProviderName: "dropbox", ProviderKey: "3443"},
{ProviderName: "facebook", ProviderKey: "768"}
]
});Let’s query the users collection by login.ProviderKey and login.ProviderName:
db.users.find({
"login.ProviderKey": "232",
"login.ProviderName": "twitter"
}).pretty();
It found the document we wanted. Let’s see how it performed:
db.users.find({
"login.ProviderKey": "232",
"login.ProviderName": "twitter"
}).explain();
Result is actually pretty bad. It scanned all four documents to find the one that we wanted to get. Let’s put an index to ProviderName and ProviderKey fields:
db.users.ensureIndex({
"login.ProviderName": 1,
"login.ProviderKey": 1
});Now, let’s see how it performs the query:
It’s better as it scanned only two documents. However, we had only one matching document for our query. As the chances that the providerKey will be more unique than the ProviderName, I want it to first look for the ProviderKey. To do that, I need to change the index:
db.users.dropIndex({
"login.ProviderName": 1,
"login.ProviderKey": 1
});
db.users.ensureIndex({
"login.ProviderKey": 1,
"login.ProviderName": 1
});Let’s now see how it’s trying to find the matching documents:
db.users.find({
"login.ProviderKey": "232",
"login.ProviderName": "twitter"
}).explain();
Boom! Only one document was scanned. This shows us how it’s important to put the fields in right order for our queries.
Resources
Hey, I'm Tugberk 👋
Coder 👨🏻💻, Speaker 🗣, Author 📚, ex-Microsoft MVP 🕸, Blogger 💻
I live in London, UK 🏡
