Indexing In MongoDB | Partial Index | TTL | Sparse Index | Unique Index

Hi guys,
In this lesson, we will talk about Mongo DB indexing. Actually we will talk about indexing creation on mongo db with options. You know that indexing is constructed from key-value pairs and it provides better performance. Actually it’s used to benefit from it. If you are familiar with hash-map, you can easily understand how it provides better performance. Because indexing has the same structure as a hash-map. So this is the advantage of indexing.
You can ask me what is the disadvantage of it. Of course it has disadvantage, when we use indexing, indexing items are also stored on another table/collection so indexing causes to another table creation. This means you need more storage. Also, when this storage increases with each insertion so your insertion/write queries also will be effected from indexing and they will be slower.
OK
Until now, we talk about common/known things on all database architectures. From now on, we will go on with MongoDB based indexing.
MongoDB database is constructed from collections. Each collection is constructed from documents. We can create indexes for collections. Index items are fields of documents. Let’s talk about an example.
First of all, we will create a database like db_users. Then we will create a users collection
Then we will generate random data to see the effect of indexing easily. To create it, we will use a typical for-each loop then we will insert data. To test it, we will insert a hundred thousand data to users collection.
Now let’s find a user via user_id without indexing:
db.users.find({user_id: 10234}).explain("executionStats");
The important thing on analyzing results is executionTime.
Now let’s create an index on user_id. We can create it like db.collectionName.createIndex. Indexes can be constructed from one or multiple fields. Our index options are: unique index (we can create it for unique fields.), partial index (we can create index for only some data of collection), sparse index: we can create index for only contain entries for documents that have the indexed field, even if the index field contains a not null value.. expireTime: we can set time-to-live property to index field. After time-to-live seconds, document will be removed. Hidden index: we can create it for debugging. When we set it to hidden, queries wont use index. We can set collation for index. We can run it on background or we we can lock the table then we can run it quickly on foreground.


Ok. Now we will create a basic index for user_id field.and we will call our find query again.
When we call find query again, we can see the execution time as decreased.


So as a result, we see the analyzing results and differences between with-indexing and without-indexing results clearly.
That’s all.
db.users.createIndex({
user_id: 1, //1: ASC, -1: DESC
//Of course, we can create compound indexes...
},
//Options
{
unique: true, //unique index; in here user_id will be unique.
partialFilterExpression: { valid: { $eq: true } }, //partial index: index for only valid users.
sparse: true, //Sparse Index: only contain entries for documents that have the indexed field, even if the index field contains a null value. In here, if a document doesnt have user_id, indexing doesnt work for it.
expireAfterSeconds: 60, //TTL(time-to-live): document indexing will be removed after ttl seconds.
hidden: true, //to debug the cases with indexing and without indexing.
collation: { locale: "tr" }, //give specific collations to language-specific rules.
background: true, //dont lock the table and create slowly index on background.
})


Now let’s find a user via user_id with using indexing:
//Firstly, let's create our basic index
db.users.createIndex({user_id:1})
//Then let's call our query
db.users.find({user_id: 10234}).explain("executionStats");
And analyze results will be:
Thank you.