Order of Fields Matters on MongoDB Indexes. Let's see how with an example.
@ 04-12-2014
by Tugberk Ugurlu


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();

image

It found the document we wanted. Let’s see how it performed:

db.users.find({
	"login.ProviderKey": "232", 
	"login.ProviderName": "twitter"
}).explain();

image

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:

image

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();

image

Boom! Only one document was scanned. This shows us how it’s important to put the fields in right order for our queries.

Resources




Hi 🙋🏻‍♂️ I'm Tugberk Ugurlu.
Coder 👨🏻‍💻, Speaker 🗣, Author 📚, Microsoft MVP 🕸, Blogger 💻, Technical Lead at Redgate 🕷 Loves travelling 🛫🛬
Lives in Cambridge, UK 🏡