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.