We’ve seen the working of explain() method briefly in previous video tutorials – in this video tutorial lets dive in to learn more about this useful method, which helps us analyze and optimize our MongoDB commands.
MongoDB shell version: 2.6.1
connecting to: test
> use temp
switched to db temp
> for(i = 1; i < = 1000; i++) db.name.insert({a: i, b: i, c: i});
WriteResult({ "nInserted" : 1 })
MongoDB shell version: 2.6.1
connecting to: test
> use temp
switched to db temp
> for(i = 1; i < = 1000; i++) db.name.insert({a: i, b: i, c: i});
WriteResult({ "nInserted" : 1 })
Here MongoDB server is returning a basic cursor, as we do not have index on field “a”. Also note that, it’s scanning 1000 documents in the “name” collection and scanning 1000 index keys in “system.indexes” collection, where the default key is “_id”.
Here we create index on key “a” and “b” – it’s called compound key – after which the explain() method shows that MongoDB server is returning a Btree cursor, meaning it’s making use of index key to execute the query/command.
Also not the number of objects scanned in the “name” collection as well as in the “system.indexes” collection.
Covered Index or indexOnly If our command is requesting for values present inside the index, mongo engine will fetch that information from the “system.indexes” collection itself and does not go to the collection where the entire document is present – in our case “name” collection. Since index is on keys “a” and “b” in our case, if we query for “a” and “b” values only, it fetches those values directly from the index information and will not go to “name” collection. Hence the speed of execution of the command is faster, and this type of commands are very efficient in creating optimized web applications.
Note from above explain() result, the number of objects scanned in the “name” collection is 0, and the number of scans made on indexes in only 1.
We know the uses of index, how to create indexes, how to delete indexes – now its time to learn the right way of creating index/key in MongoDB for its optimum usage.
temp: database name stu: collection name
Inserting 10 Million Documents
1
2
3
4
5
use temp
switched to db temp
for(i=1; i < = 10000000; i++)
db.no.insert({"student_id": i});
use temp
switched to db temp
for(i=1; i < = 10000000; i++)
db.no.insert({"student_id": i});
This inserts 10 Million documents inside “stu” collection. It takes atleast a minute or so to complete the insertion – patience is key!
Since there are 10 Million documents inside “stu” collection, it takes some time to create index on “student_id” key.
Things to note: 1. By default, index creation in MongoDB takes place in the foreground. 2. It’s faster than background index creation. 3. It blocks other write operation 4. Ideal to use when in development system – as you’re the only one writing to the server(database). Also ideal to use when you’ve replica sets – where you’ll have multiple set of mongod with same data set to operate on, in which case you can have index information on a separate replica set which helps in faster and efficient index creation.
Simply pass in the option in second parameter to ensureIndex() method – {background: true}
It takes time to complete the process, as there are 10 Million documents in the collection.
Things to note: 1. It’s slower than foreground index creation method. 2. It does not block any other write operation. 3. Ideal in production server/system. As there’ll be other people who’ll be performing read and write operation on the same database server. 4. Ideal when you’re not making use of replica sets yet.
All sparse indexes are unique index, but not all unique indexes are sparse! There are situations where we want to create unique index on key/field which is not present in all documents. Wherever the key is not present, it’s value will be treated as NULL. If more than 1 document has NULL value to the key we want to make as unique, then it violates unique key rule. In such situations we can make use of Sparse index and create unique key on only those documents which has the key in it.
Since last 2 documents do not have “product” key, its value will be treated as “NULL”. Since both these documents have value of “product” as “NULL”, trying to create unique key on “product” throws duplicate key error.
This command sorts all the documents in lexicographical order. The output includes all the documents. Those documents which do not have “product” key in them are listed first.
Since the command is even listing/sorting the documents which do not have “product” key in them, it’s simply making use of Basic Cursor – which do not help in optimizing the command/query performance.
hint() method tells the mongoDB server to operate only on the sparse key.. hence only retrieving and operating on the documents which has the sparse key in them.
After using hint() method, mongoDB server is only looking for documents which has the sparse index key in them, so it can directly look for the sparse index key inside “system.indexes” collection, hence it’s using Btree Cursor – and is efficient.
We learnt how to create unique key/index using {unique: true} option with ensureIndex() method. Now lets see how we can create unique key when there are duplicate entries/documents already present inside the collection.
Since “_id” is treated as primary key in mongoDB, we can’t insert duplicate values to it. In above case, we are trying to insert value of “_id” as 1 twice – the second time around it threw an error stating the entered value as duplicate.
Before implementing unique key on field “a” we need to first remove the duplicate entries present inside our collection orelse it’ll through errors. Here we also remove the index/key on “a”, so that we can create unique key/index on “a”.
To create unique key/index, we need to make use of ensureIndex() method – first parameter being the field name to be made as unique key along with it’s value 1 or -1. 1 signifies ascending order, -1 signifies descending order. The second parameter {unique: true}, specifies that the key/index must be unique key/index, like that of “_id”.