Indexing in mongoDB in detail

You are here : Home / MongoDB Tutorial



Contents of page >










1) Indexing in collection (table) in mongoDB >


Indexes speeds up the query because with indexes MongoDB doesn't scan every document of a collection.
Hence results are retrieved very quickly in extremely efficient manner without scanning such large data.


1.1) What are indexes?
Indexes are data structures.
Indexes store a very small portion of the data, so that data could be easily retrieved.


1.2) We will use ensureIndex method of mongoDB to create indexes.


1.3) Let's create collection and insert records in it before indexing>
> db.employee.insert({empId : 1,  firstName:"ankit"})
> db.employee.insert({empId : 2,  firstName:"sam"})
First line above will create table (or collection) (if collection already exists it will insert records in it).


1.4) Let's see what is there in collection before indexing >
> db.employee.find();
{ "_id" : ObjectId("584ebed11127dea5ece72742"), "empId" : 1, "firstName" : "ankit" }
{ "_id" : ObjectId("584ebee21127dea5ece72743"), "empId" : 2, "firstName" : "sam" }
>


1.5) First let’s see default indexes on collection employee >
> db.employee.getIndexes()
[
       {
               "v" : 1,
               "key" : {
                       "_id" : 1
               },
               "name" : "_id_",
               "ns" : "mydb.employee"
       }
]
>


By default there is index on _id field.
Where
v is the index format version
Key contain field on which index is there >  _id is the field name.
name is the index name (If not specified it is generated automatically by mongoDB).
Ns is databaseName.collectionName


1.6) Now, let’s create index on field empId >
> db.employee.ensureIndex({empId : 1})
Now, index have been created on field empId.
Here 1 is to create index in ascending order.
You can also use -1 is to create index in descending order.


1.7) Now, let’s see indexes on collection employee after creating index on field empId >
> db.employee.getIndexes()
[
       {
               "v" : 1,
               "key" : {
                       "_id" : 1
               },
               "name" : "_id_",
               "ns" : "mydb.employee"
       },
       {
               "v" : 1,
               "key" : {
                       "empId" : 1
               },
               "name" : "empId_1",
               "ns" : "mydb.employee"
       }
]


2) Create index on multiple fields(compound index) in collection in MongoDB >
> db.employee.ensureIndex({empId : 1}, {firstName : 1})


3) Drop index on fields in collection >
> db.employee.dropIndex({empId:1});


4) Important things you must know before creating indexes in MongoDB >


4.1) Never create extra indexes >
  • Space overhead - Because indexes occupies space.
  • Performance and RAM - Indexes are stored in RAM, creating too many indexes will occupy too much ram and other processes of system might suffer in that case.
  • If collection is used less then don’t create indexes.


4.2) Where we cannot use indexes ?
Indexes cannot be used in queries which have $where clause.
Indexes cannot be used in queries which have Regular expressions.
Indexes cannot be used in queries which have Arithmetic operators


4.3) How many indexes a collection can have?
A collection can have maximum of 64 indexes in it.


4.4) How many fields can be included in compound index?
A compound index can have maximum of 31 fields in it.


4.5) Is there any limitation on length of index name?
Index can have maximum length of 125 characters in it.


Text indexes in MongoDB - Creating, query and dropping text index


5) Creating indexes on array in MongoDB >


Now, let's create index (Multikey index)  on array.


5.1) First, create and insert in STUDENT collection >
db.STUDENT.insert({
 _id: 1,
 FIRST_NAME: "Ankit",
 PHONE: [    1234, 2345   ]
})


5.2) Now, create a index (Multikey index) on array >
To create a index (Multikey index) on array, use the db.collection.createIndex() method:

5.3) Whenever we create index on array field MongoDB automatically creates a multikey index. We need not to explicitly specify the multikey type.
db.STUDENT.createIndex( { PHONE : 1 } )


5.4) Let’s understand above line >
Above, we created index of array field PHONE in ascending order. By using -1 we can create index in descending order.
The index will be created which following contains two index keys, each of them points to the same document.
  • 1234,
  • 2345
As it contains two index keys - That’s why it’s called Multikey index on arrays.


5.6) Query 1 : Let’s find the document with array field in document using index (Multikey index) >
db.STUDENT.find( { PHONE : 1234   } )
Output>
{ "_id" : 1, "FIRST_NAME" : "Ankit", "PHONE" : [ 1234, 2345 ] }


5.7) Query 2 : Let’s find the document with array field in document using index (Multikey index) >
db.STUDENT.find( { PHONE : [ 1234, 2345  ] } )
Output>
{ "_id" : 1, "FIRST_NAME" : "Ankit", "PHONE" : [ 1234, 2345 ] }


MongoDB can use the index (multikey index) to find documents that have 1234 at any position in the PHONE array. Then, MongoDB retrieves these documents and filters for documents whose PHONE array equals the query array [ 1234, 2345 ].

6) Limitations on index (Multikey index) on array  in MongoDB>
6.1) We cannot create compound index (compound Multikey index) on array
6.1.1) Let's say collection is like this >
db.STUDENT.insert({
 _id: 1,
 FIRST_NAME: "Ankit",
 PHONE: [    1234, 2345   ],
 X: [    1, 2   ]
})


6.1.2) Now, we cannot create compound index (compound Multikey index) on array


Example- We cannot create below index because both PHONE and X are array fields.


db.STUDENT.createIndex( { PHONE: 1 , X : 1 } )
Above, line is invalid and any attempt to execute above will throw error  "exception: cannot index parallel arrays [X] [PHONE]"


6.2) But, you CAN create compound index (compound Multikey index) when we are indexing on non-array and array field >


6.2.1) create and insert in STUDENT collection >
db.STUDENT.insert({
 _id: 1,
 FIRST_NAME: "Ankit",
 PHONE: [    1234, 2345   ]
})


6.2.2) Now, let's create compound index (compound Multikey index) when we are indexing on non-array field and array field.


Example- We can create below index because
PHONE is array but
FIRST_NAME is non-array field.


db.STUDENT.createIndex( { FIRST_NAME : 1, PHONE : 1 } )


6.2.3) Query 1 : Let’s find the document with compound index (compound Multikey index) from non-array and array field >
db.STUDENT.find( { PHONE : 1234   } )
Output>
{ "_id" : 1, "FIRST_NAME" : "Ankit", "PHONE" : [ 1234, 2345 ] }

6.2.4) Query 2 : Let’s find the document with compound index (compound Multikey index) from non-array and array field >
db.STUDENT.find( { FIRST_NAME : "Ankit", PHONE : 1234 } )
Output>
{ "_id" : 1, "FIRST_NAME" : "Ankit", "PHONE" : [ 1234, 2345 ] }




7) Let's create compound index (compound Multikey index) on fields of array with Embedded Documents >


7.1) create and insert in STUDENT collection >
db.STUDENT.insert({
 "_id": 1,
 "FIRST_NAME": "Ankit",
 "PHONE": [
   {"PHONE_NUMBER": 1234, "PHONE_ID": 11 },
   {"PHONE_NUMBER": 2345, "PHONE_ID": 12, }
 ]
})
db.STUDENT.insert({
 "_id": 2,
 "FIRST_NAME": "Ankit",
 "PHONE": [
   {"PHONE_NUMBER": 3456, "PHONE_ID": 13 },
   {"PHONE_NUMBER": 4567, "PHONE_ID": 14, }
 ]
})


7.2) Now, let's create compound index (compound Multikey index) on fields of array with Embedded Documents >
db.STUDENT.createIndex( { "PHONE.PHONE_NUMBER" : 1, "PHONE.PHONE_ID" : 1  } )


7.3) Query 1 : Let’s find the document using compound index ( compound Multikey index) on fields of array with Embedded Documents>
db.STUDENT.find( { "PHONE.PHONE_NUMBER" : 1234, "PHONE.PHONE_ID" : 12   } )
Output>
{ "_id" : 1, "FIRST_NAME" : "Ankit", "PHONE" : [ { "PHONE_NUMBER" : 1234, "PHONE_ID" : 11 }, { "PHONE_NUMBER" : 2345, "PHONE_ID" : 12 } ] }


7.4) Query 2 : Let’s find the document using compound index ( compound Multikey index) on fields of array with Embedded Documents>
db.STUDENT.find( { "PHONE.PHONE_NUMBER" : 1234  } )
Output>
{ "_id" : 1, "FIRST_NAME" : "Ankit", "PHONE" : [ { "PHONE_NUMBER" : 1234, "PHONE_ID" : 11 }, { "PHONE_NUMBER" : 2345, "PHONE_ID" : 12 } ] }


8) Creating Text index in MongoDB>
8.1) create and insert in STUDENT collection, before creating text indexes >
db.STUDENT.insert({
 _id: 1,
 X: "first name abc",
 Y: "last name def "
})

db.STUDENT.insert({
 _id: 2,
 X: "first name lmn",
 Y: "last name xyz"
})


IMPORTANT to know : A collection can have maximum of one text index at a time.


8.2) Now, lets create text index on collection >
db.STUDENT.createIndex( { X : "text" } )
We created text index on the field X.


8.3) Query : Search text in collection using text-index >
We will use $text and $search operators. It will search in indexed locations only (i.e. in field X only).
db.STUDENT.find({$text:{$search:"lmn"}})
Output>
{ "_id" : 2, "X" : "first name lmn", "Y" : "last name xyz" }

8.4) Query : Search phrase using text index >
Search documents whose indexed field contains "name" or " lmn"
We will use $text and $search operators.
db.STUDENT.find({$text:{$search:"name lmn"}})
Output>
{ "_id" : 1, "X" : "first name abc", "Y" : "last name def " }
{ "_id" : 2, "X" : "first name lmn", "Y" : "last name xyz" }


8.5) Search documents whose indexed field contains "name" but doesn’t include "lmn". (Basically exclude results with "lmn")
We will use $text and $search operators.
db.STUDENT.find({$text:{$search:"name -lmn"}})
Output>
{ "_id" : 1, "X" : "first name abc", "Y" : "last name def " }

8.6) We can index multiple fields of collection for the text index >
db.STUDENT.createIndex( { X : "text",  Y : "text" } )
We created text index on the fields X and Y.


8.7) WILDCARD text index on collection >
db.STUDENT.createIndex( { "$**" : "text" } )
Wildcard text indexes creates text indexes on multiple fields.

9) Creating text index on array-field in collection >
9.1) create and insert in STUDENT collection >
db.STUDENT.insert({
 _id: 1,
 X: "first name abc",
 PHONE: [ "12", "34" ]
})

db.STUDENT.insert({
 _id: 2,
 X: "first name lmn",
 PHONE: [ "56", "78" ]
})


9.2) Now, lets create text index on array-field in collection >
db.STUDENT.createIndex( { PHONE : "text" } )
We created text index on the array-field PHONE.


9.3) Query : Search text in array-field using text-index >
We will use $text and $search operators. It will search in indexed locations only.
db.STUDENT.find({$text:{$search: "12"}})
Output>
{ "_id" : 1, "X" : "first name abc", "PHONE" : [ "12", "34" ] }


10) Drop text index in MongoDB >


First find the name of text index in collection>
db.STUDENT.getIndexes()
Output >
[
       {
               "v" : 1,
               "key" : {
                       "_id" : 1
               },
               "name" : "_id_",
               "ns" : "mydb.STUDENT"
       },
       {
               "v" : 1,
               "key" : {
                       "_fts" : "text",
                       "_ftsx" : 1
               },
               "name" : "X_text",
               "ns" : "mydb.STUDENT",
               "weights" : {
                       "X" : 1
               },
               "default_language" : "english",
               "language_override" : "language",
               "textIndexVersion" : 2
       }

]
Highlighted X_text is the name of index which we want to delete.


db.STUDENT.dropIndex("X_text");


11) SUMMARY  >
1) Indexing in collection (table) in mongoDB >
Indexes speeds up the query because with indexes MongoDB doesn't scan every document of a collection.


1.3) Let's create collection and insert records in it before indexing>
> db.employee.insert({empId : 1,  firstName:"ankit"})


1.6) Now, let’s create index on field empId >
> db.employee.ensureIndex({empId : 1})


2) Create index on multiple fields(compound index) in collection >
> db.employee.ensureIndex({empId : 1}, {firstName : 1})


3) Drop index on fields in collection >
> db.employee.dropIndex({empId:1});


4) Important things you must know before creating indexes in MongoDB >
Never create extra indexes, it might lead to Space overhead, Performance issues.
A collection can have maximum of 64 indexes in it.
A compound index can have maximum of 31 fields in it.
Index can have maximum length of 125 characters in it.


5) Creating indexes on array in MongoDB >
Now, let's create index (Multikey index)  on array.
5.1) First, create and insert in STUDENT collection >
db.STUDENT.insert({
 _id: 1,
 FIRST_NAME: "Ankit",
 PHONE: [    1234, 2345   ]
})

5.3) Whenever we create index on array field MongoDB automatically creates a multikey index.
db.STUDENT.createIndex( { PHONE : 1 } )


5.6) Query 1 : Let’s find the document with array field in document using index (Multikey index) >
db.STUDENT.find( { PHONE : 1234   } )
Output>
{ "_id" : 1, "FIRST_NAME" : "Ankit", "PHONE" : [ 1234, 2345 ] }


6) Limitations on index (Multikey index) on array >
We cannot create compound index (compound Multikey index) on array
But, you CAN create compound index (compound Multikey index) when we are indexing on non-array and array field >


7) Let's create compound index (compound Multikey index) on fields of array with Embedded Documents >


7.1) create and insert in STUDENT collection >
db.STUDENT.insert({
 "_id": 1,
 "FIRST_NAME": "Ankit",
 "PHONE": [
   {"PHONE_NUMBER": 1234, "PHONE_ID": 11 },
   {"PHONE_NUMBER": 2345, "PHONE_ID": 12, }
 ]
})


7.2) Now, let's create compound index (compound Multikey index) on fields of array with Embedded Documents >
db.STUDENT.createIndex( { "PHONE.PHONE_NUMBER" : 1, "PHONE.PHONE_ID" : 1  } )


7.3) Query 1 : Let’s find the document using compound index ( compound Multikey index) on fields of array with Embedded Documents>
db.STUDENT.find( { "PHONE.PHONE_NUMBER" : 1234, "PHONE.PHONE_ID" : 12   } )


8) Creating Text index in MongoDB>
8.1) create and insert in STUDENT collection, before creating text indexes >
db.STUDENT.insert({
 _id: 1,
 X: "first name abc",
 Y: "last name def "
})


IMPORTANT to know : A collection can have maximum of one text index at a time.


8.2) Now, lets create text index on collection >
db.STUDENT.createIndex( { X : "text" } )


8.3) Query : Search text in collection using text-index >
db.STUDENT.find({$text:{$search:"lmn"}})


8.4) Query : Search phrase using text index >
db.STUDENT.find({$text:{$search:"name lmn"}})


8.7) WILDCARD text index on collection >
db.STUDENT.createIndex( { "$**" : "text" } )


9) Creating text index on array-field in collection >
9.1) create and insert in STUDENT collection >
db.STUDENT.insert({
 _id: 1,
 X: "first name abc",
 PHONE: [ "12", "34" ]
})


9.2) Now, lets create text index on array-field in collection >
db.STUDENT.createIndex( { PHONE : "text" } )


9.3) Query : Search text in array-field using text-index >
db.STUDENT.find({$text:{$search: "12"}})


10) Drop text index >
First find the name of text index in collection>
db.STUDENT.getIndexes()
Output >
….
               "name" : "X_text",
….


db.STUDENT.dropIndex("X_text");


Having any doubt? or you liked the tutorial! Please comment in below section.
Please express your love by liking JavaMadeSoEasy.com (JMSE) on facebook, following on google+ or Twitter. You may join our fbGroup or linkedInGroup as well.

RELATED LINKS>

What is MongoDB - A quick introduction to database



Drop index on fields in collection in MongoDB

Creating indexes (Multikey index) and compound indexes on array in MongoDB - And its Limitations

Text indexes in MongoDB - Creating, query and dropping text index


eEdit
Must read for you :