MongoDB indexes

MongoDB indexes

>Introduction
Indexes are to optimize read, update and delete operation.
Indexes load a extra funcnionality while inserting data into DB, mongoDb update indexes whenever a record in inserted
db.contacts.getIndexes() // to get all the indexes

> Why index are needed
By default MongoDB do COLLECTION lookup while read, update and delete operation which is time consuming in sense that 
Thare is unknown number of records that match condition 
records position is unknown to mongoDB , HENCE mongoDB iterate over each document looking for matching condition 
Solution : we can create an index on the column which is frequently used to access records.
by this way all the records will be sorted and they have a referance in main document.
In this case mongoDB will apply Index lookup.

>Adding and index and explain()
explain() this method works with find, update ,delete methods it do not work with insert method 
this method gives details about the query executed 
Syntax: db.contacts.explain().find({"dob.age":{$gt:60}})
above line will give detail about execution plan 
db.contacts.explain("executionStats").find({"dob.age":{$gt:60}}) // 5 millisecond
above line will give more detail about query executed like "totalKeysExamined", "totalDocsExamined"

db.contacts.createIndex({"dob.age":1}) // this will create an index on age column 
db.contacts.explain("executionStats").find({"dob.age":{$gt:60}})  // 2 milisecond 
as we can see time to execute is reduced drastically also Totaldocsexamined is reduced.

> Adding conpound index 
Index can be applied to not only integer column but also string , Do not use it for boolean as there is only 2 value true and false
this might no improve performance 
*** drop index : db.contacts.dropIndex({"dob.age":1}) // this will drop the indexes

Compound index : db.contacts.createIndex({"dob.age":1,gender:1})
here order of element in index does matter, Also the index will be stored in one collumn rather than 2 column like 
[age:30,gender:"male"]
[age:30,gender:"female"]
so while doing indexScan mongoDB will first match age then it will go for gender
Note this compund index can be also used for getting age as only filter as age is comes first in index list but it can't be used for 
gender fintering.

compound index rules :
if index is on column A,B,C,D then this compund index can be used for searching (A B C D),(A B C), (A B), (A) So index flow from left
to right.

> Using index for Sorting 
indexing does the sorting on the column, this is default behaviour.
Internaly mongoDB uses indexes for sorting 
db.contacts.explain("executionStats").find({"dob.age":{$gt:60}}).sort({gender:1})

>Default indexes 
mongodb  crate a default index ID , this index is unique per document 
> creating Unique Index
let suppose we want to create an index based on users email id, as email id will be unique per document 
db.contact.createIndex({email:1},{unique:true})
Note If there will be unique records of email then index will be created other wise there will be an error 

> Creating partial index 
Concept: let suppose we know that we will use index based on age but we are also certain that the gender will be "male"
now if we create a normal index on dob.age then it will create an index including all gender , eventually it will increase 
the index size and stand out as a inefficient index.

now using partial index we can put a conditional index wheneve there will be filter based on age it will go for COLLSCAN but 
whenever gender is specefied it will go for IDXScan
db.contact.createIndex({"dob.age":1},{partialFilterExpression:{gender:"male"}})
above query will create an index based on age and gender wher gender is very specefic 
Note we can also create a compoung index on age and gender but all the gender will be included in that index 

 db.contact.explain().find({"dob.age":{$gt:60}}) // COLL SCAN WILL be applicable
 db.contact.explain().find({"dob.age":{$gt:60},gender:"male"}) // INDEX SCANN will be applcable 
 db.contact.explain().find({"dob.age":{$gt:60},gender:"female"})  // COLLL SCAN will be applicable as gender female
 
 So by using partial index we reduced index size drastically
 
> Time to live index 
concept: let suppose we have some field on document which is "date type", we can create an index on this date type 
but there is one more imp conecpt this index will make records disappear after specefied ammount of time.

db.session.createIndex({createdAt :1},{expireAfterSeconds:10})
db.sessions.insertOne({data:"dfsfdsf",createdAt:new Date()}) this data will be deleted after 10 seconds 

> Diagnose query 
Till here we learned how to apply index and different options that comes with index 
now we will learn how to diagnose query execution 
we already know option comes with explain() method 
explain("queryPlanner") // this will give executed query and winning plan 
explain("executionStats")  // this will give executed query and winning plan and Rejected Plan
explain("allPlansExecution")  //  this will give executed query and winning plan and Rejected Plan and Winning plan decisio nprocess

>Covered Queries
 "totalDocsExamined" : 0 
 if total docs examined is 0 that means mongoDB is retuning data directly from indexes
 db.contact.explain("executionStats").find({"dob.age":{$gt:60}},{"dob.age":1,_id:0})
 here above we are getting only age from query , if something else is projected from db then document scan will get increased

> How mongoDB reject Plan
when working with indexes order of element in an index is very inportant, but this order is not imp when writing a query
most general purpose fields are kept on left and other are on right.
when executing query mongodb might have multiple indexes for quering a data, so mongodb set a thresold to find those records 
using all the approch. the approch which reach thresold faster wins, then mongo db store this winning plan into the cache for
similar king of query. 
but it reconsider when one on following things happpen
1) write in db (>1000) 2) add or delete index 3) index rebuilt 4) MongoDB server restarted

> Multi key index 
Till now we learned 2 types of idnex SINGLE and COMPOUND
MultiKey Index 
mongodb allow to store index based on array values , it scatter entire array and store as index .
db.temp.insertOne({name:"Abhinay",hobbies:["Cooking","Sports"],address:[{street:"main Street"},{street:"second Street"}]})
there are two array one with direct value and other with array of document 
db.temp.createIndex({hobbies:1})   // this will create index for each element in an array
db.temp.createIndex({addreses:1})  // this will create index for each document in an array 
db.temp.explain("executionStats").find({"address.street":"main Street"})  /// this will not use index scan as index are stored
as document, to use index  we should take other approch 
 db.temp.explain("executionStats").find({"address":{street:"main Street"}})  // this will use index scan as query is asking some 
 socument within address to have some value 
 
 we can also directly create an index on sub document level 
 db.temp.createIndex({"address.street":1})
 db.temp.explain("executionStats").find({"address.street":"main Street"}) // now we can directly refer street within address , this 
 will return an index scan
 Restriction : compund index of two multikey fields can not be created, as it have to store cartician product of two array.

> Text Index 
This is a better alternative to text search regex 
regex are very costly hence we have an alternative to search based on index 
with text index we select a field in a document for text search, ** note there can be only ONE TEXT INDEX in a collection

 db.products.insertMany([
 {title:"A-Book",desc:"This is awesome book about a young artist"},
 {title:"Red T-Shirt",desc:"This is red and pretty awesome"}])
 
db.products.createIndex({desc:"text"}) // this will create an index on "desc" field // it will not include the, is , a , an etc 
 db.products.find({$text:{$search:"awesome"}}) // To find some text use query like this.
 db.products.find({$text:{$search:"red book"}}) // this will return both the document as book is in first and red in second
to make something exclusive within a document use this 
db.products.find({$text:{$search:"\"red and pretty\""}})

>Creating combined text index
As we can not create another text index on a collection we can merge two fields
first dropt existing text index db.products.dropIndex("desc_text")
now  specify all the fields which should be merged as text index 
db.products.createIndex({desc:"text",title:"text"})

** exclude some word from search criteria
let suppose we want to find awesome but not t-shirt
 db.products.find({$text:{$search:"awesome"}}) // this will give all records with awesome but we want to restrict t-shirt
{ "_id" : ObjectId("5cd7e2e3e0cef38549630695"), "title" : "Red T-Shirt", "desc" : "This is red and pretty awesome" }
{ "_id" : ObjectId("5cd7e2e3e0cef38549630694"), "title" : "A-Book", "desc" : "This is awesome book about a young artist" }

we can use "-" for this 
db.products.find({$text:{$search:"awesome -t-shirt"}})  ** note - is suffix for t-shirt
{ "_id" : ObjectId("5cd7e2e3e0cef38549630694"), "title" : "A-Book", "desc" : "This is awesome book about a young artist" }

>building index 
index are created in background and foreground 
till now we have created index in foreground, this locks the collection and it is faster 
using background collection will be still accessible 
db.collection.createIndex({indexname:1},{background:true})