Blog

MongoDB Create Index

  • (4.0)
  • | 1626 Ratings

MongoDB Create Index


Indexes in a database are pretty much the same as the ones we see in front of each book. An Index in a database is used to carry out the Search or Select any data from the database faster. For example, if you want to search for a particular topic in a book, you can refer to the index table, specifying each topic alphabetically, and jump directly to the page or pages that have the topic.


Indexes in MongoDB can be termed as a special set of data that contains qualified or limited information related to any specific data column. Since the data stored in Indexes is partial , it gets easier to read the data and make the query execute faster. with the use of Indexes, executing operations becomes more easy in MongoDB. Indexes in MongoDB can be created easily and without affecting the data. It can be Unique also which helps to prevent duplicate entries in the index table. Now, let’s have a detailed knowledge of this highly advanced technology and its use in the current IT environment. 



Importance of Indexes


While accessing any data, IT professionals used to search and dig out the data from the database using sequential scan i.e. looking for that particular data one by one from the whole database before returning the result.


  • Having an index table in the database helps the user to execute the query faster.
  • Using the index with Unique key also helps the user to avoid retrieving duplicate data.

[Related Blog: How To Sort Data With MongoDB?]


However, Indexes can slow down any query with Update or Insert statements.


The main importance of Indexing is to minimize the time and execute any query faster by reducing the number of tables or columns that need to be checked. Though creating an index totally depends on the user, it is recommended to index the primary key. 

 
 
To gain in-depth knowledge and be on par with practical experience, then explore MongoDB Training Course.
 

Impact of Indexes


Just like any other technology, or we can say methodology, Indexes also make an impact on the current working architecture. Let us have a look at how Indexes can make an impact on current IT:


  • Indexes can either be defined for only one field from the collection or can be based on multiple fields from the collection.
  • Above mentioned points regarding Indexes already established that it is better to have an Index in any queries in a database. However, making more indexes in any database can slow down the operations.
  • Operations such as Update or insert statements needs to access the database to make changes in any column or rows. The Indexes also need to be updated every time, which results in degradation of the execution performance for any query. 
  • Adding Indexes to a database can affect the performance to write or update in any database directly. 

Apart from this, we are also showcasing an example here, which represents the working of an Index. The example comprises two indexes, which are used in the collection i.e. “Employeeid: 1” and “Employee Code: AA”. Now, when a search query is made, the indexes will immediately get effected and return the particular file from the collection as a result. Kindly take a look at the following multiple indexes in the below-mentioned example.


The example simply shows that if any query is associated with either of the indexes, it would return that document. However, having multiple indexes can degrade the performance. If any query contains update or Insert operations, it will slow down the execution of the query.


[Related Blog: MongoDB Create Collection]


How to Create Indexes?


Just like any other digital methodology, MongoDB also has some predefined scripts and syntaxes, which need to be accessed to make the desired operations executed. In order to create an Index,  “createIndex”  method is used in MongoDB. The syntax of creating an index:


db.collection.createIndex (keys, options)


To make it completely understandable, let's take an example that elaborates how to create Indexes in MongoDB. Suppose we have a collection in the database containing “Employeeid” and EmployeeName” as field names.


db.Employee.createIndex({Employeeid:1])


Here, the createIndex method is used to create the Index “Employeeid”. The parameter ‘Employeeid’ is used to indicate the constraint or the field on which our index is based on. The parameter ‘1’ dictates that the field values of employeeid are sorted in ascending order.


The output of the following would be. 


This above-mentioned example depicts how to create an Index in MongoDB. This also shows the index created based on only one field value. However, as stated above we can create an index based on multiple field values also.


db.Employee.createIndex({Employeeid:1, EmployeeName:1])


Here, in this code we can see that the index is based on two field values i.e. Employeeid: 1 and EmployeeName: 1 where the 1 indicating the ascending order.


[Related Blog: How To Change MongoDB Default And Connect With MongoDB]


Definition: Parameters are a set of fields or columns that define the condition for the operation of any query. Creating an Index in MongoDB requires parameters. The parameters inform the query on which field-values the index must be created and how it is used.


Parameter Type Description
Keys Document

A document containing the fields as index keys and values indicating the order of that index key. 

 

Value ‘1’ refers to ascending order on a field whereas the value ‘-1’ refers to descending order.

 

 

The indexes in MongoDB can be numerical, text, hashed ( eg: _id) and geospatial ( eg: 2dsphere)

 

Options Document Optional. A set of options that specify or control the creation of any index. This is optional and can be used as per the requirement.

Options


The options document consists of a group of options that specify or control the establishment of any index. This is optional and can be used as per the requirement. 


[Related Blog: MongoDB Vs MySQL - Which Is A Better Database?]


Options for All Index Types


Though Options for indexes mentioned below are optional, they can be used with all indexes unless the type of index has been already specified.


Parameter Type Description
background boolean Background, as the name suggests, makes the index background to make sure that the operation doesn’t have any effect on other operations of the database. However, one needs to specify the value to be true as to set it in the background. The default value is false.
unique boolean

Unique option helps to create a unique index. It will help to reduce the time when performing insertion or update of documents. It denies the operations wherever the existing value and the index key value in the index are the same.

to create a unique index you need to specify the value to be true as the default value is false.

partialFilterExpression document This option is new in MongoDB version 3.2. It is also preferred over the sparse option. Using filters helps the index to refer to only those documents which fall under or matches the filters expressions.
sparse boolean This option is similar to partialFilterExpression. The Sparse index only refers to those documents which specify the condition. Less space is consumed by these indexes. They also react to queries differently in some situations (particularly in sorting).
expireAfterSeconds integer Specifies a TTL (time-to-live) value that is numeric, to denotes the seconds, as to control how much time it takes MongoDB to hold onto documents in this collection. Though this option can only be used with Time-To-Live indexes.
storageEngine document

This option allows users to optimize the storage engine on a per-index basis when creating an index.

The storage-engine option should take the following form:

storage-engine: { : }

This option is specified when creating indexes are validated and logged to the oplog (OperationLog: keeping a record of every modification done on data) during duplication to support the copy sets with members that use different storage engines.


Options for Collation


Collation option in MongoDB is introduced in version 3.4. Therefore it does not support MongoDB 3.2 or earlier versions.


Parameter Type Description
collation document Collation in MongoDB 3.4 comes with a set of rules used to compare strings that are defined in a particular language. If no language has been specified then the strings are sorted on the basis of binary differences.

The syntax of collation is as follows:


collation: {
   locale: <string>,
   caseLevel: <boolean>,
   caseFirst: <string>,
   strength: <int>,
   numericOrdering: <boolean>,
   alternate: <string>,
   maxVariable: <string>,
   backwards: <boolean>
}

Other than Locale field, all of the remaining fields are optional. Locale field is mandatory and needs to be filled with genuine information. Indexes that do not support Collation are: Text Indexes, 2d Indexes, and geoHaystach Indexes.


Check out MongoDB Interview Questions


Collation and Index Use


In MongoDB, a single index allows only one collation. However, if you need to specify more than one collation, you need to create multiple indexes. If the operation defines different collation to the indexed fields then the query cannot execute the string comparison as the index with collation would not support it.


[Related Blog: MongoDB Find Queries ]


Therefore, for the comparison of strings using the index, we must also initialize the same collation.


Let's understand this with an example.


CREATE TABLE test1c (
    id integer,
    content varchar COLLATE "x"
);
CREATE INDEX test1c_content_index ON test1c (content);

The collation of the underlying column is used by index blindly. 


SELECT * FROM test1c WHERE content > constant;

[Related Blog: MongoDB vs PostgreSQL Comparison]


The index can be accessed by a query of any form, as the collation of the column will be used by default in comparison of strings. However, if the queries contain some other specified collation then this index cannot accelerate the queries. For example,


SELECT * FROM test1c WHERE content > constant COLLATE "y";

Here we can create a new index supporting the collation ‘y’ like this:


CREATE INDEX test1c_content_y_index ON test1c 
(content COLLATE "y");

[Related Blog: MongoDB Aggregate]

 

Options for text Indexes


Options available only for text field Indexes are labeled as options for text Indexes.


Parameter Type Description
weights document Optional. The weight consists of integer values with a range from 1 to 99,999 and represents the importance of the field with respect to other indexes as a score. You can declare weights for some or all the indexed fields. 1 is the default value.
default_language string Optional. For text indexes, the language that controls the list of words. It constructs the rules for the stemmer and tokenizer. See Text Search Languages for the available languages and state a Language for Text Index. The default value is English.
language_override string Optional. For text indexes, the name of the field, in the collection’s documents, that contains the overriding language for the document. The default value is language. 
textIndexVersion integer Optional. The text index version number is used to override the default version number.

Options for 2dsphere Indexes


2dsphere indexes have a specific option based on the user's requirement. The 2dsphere indexes are used in queries to obtain the geometries on the earth-like sphere.  Whether it is intersection, inclusion or proximity, 2dsphere supports all MongoDB geospatial queries.


Syntax: db.collection.createIndex ( { : "2dsphere" } )


Parameter Type Description
2dsphereIndexVersion integer

Optional. The 2dsphereIndexVersion, you can use this option to specify or override the default version number.

MongoDB 3.2 and above this version have version 3 as default 2dsphereIndexVersion

MongoDB 2.6 and up to 3.0 have version 2 as default 2dsphereIndexVersion


Options for 2d Indexes


The 2d indexes are used for the data that are stored in a two-dimensional plane. With multiple 2d indexes in a collection, a key option must be specified so that we can use the indexed field path.


[Related Blog: MongoDB Show Collections]


Options for 2d Indexes are:


Parameter Type Description
bits integer

Optional. For 2d indexes, the bits option store geohash value of the location data.

The bits value ranges from 1 to 32 inclusive. However, it has a default value of 26.

min number Optional. For 2d indexes, the min option is used to store the less or equal value for the latitude and longitude values. it has a default value of -180.0.
max number Optional. For 2d indexes, the max option is used to store the equal or more than value for the latitude and longitude values. it has a default value of 180.0.

Options for geoHaystack Indexes


A geoHaystack index is optimized in such a way that it helps in returning the results over small areas.


[Related Blog: MongoDB Update Document]


Options for geoHayastack are:


Parameter Type Description
bucketSize number In geoHaystack indexes, the bucket size option is used to group or couple those location values in the same bucket that have the specified number of units. However, it is important to note that the value must be always greater than 0.

Behaviors


The behavior of the createIndex () method is mentioned below:


  • In order to update or insert new index options other than collation, you must drop the index using the dropIndex() method. Then you have to create a new index by the help of createIndex() method specifying the new options.
  • Creating a new index with different options while having an existing index of the same index fields, it will create a conflict in the indexes. Hence the db.collection.createIndex() cannot modify the existing index even if the options are different.
  • However, collation allows multiple indexes with the same keys provided the collations are different. To create this type of indexes i.e. same key names yet different collation, you have to provide unique names to the indexes. 
  • Creating the same indexes with same options will not affect the process. As if this practice is implemented it will only execute the first operation and all other operations will not have any effect.
  • The Index for an existing document should not overpass the maximum key length of the index otherwise MongoDB will not create the index. The versions before 3.2 would allow this operation i.e. to create index however it won’t index such documents.

Check Out MongoDB Tutorial


Examples:


Create an Ascending Index on a Single Field


In order to create an index with ascending order, a value of 1 must be specified with the key field.


[Related Blog: MongoDB vs. CouchDB]


For example:  Consider the following collection 


{
  "_id": ObjectId("570c04a4a459"),
  "score": 103,
  "location": { state: "NY", city: "New York" }
}

Now to sort the index in ascending order: db.records.createIndex( { score: 1 })


The following example creates an ascending index on the field score.


[Related Blog: MongoDB Docker Container Creation With Example ]


Create an Index on a Multiple Fields


A compound index or index with multiple fields can be created using the below mentioned syntax: db.collection.createIndex( { : , : , ... } )


In multiple field indexes, the sort order of the index plays a vital role. It determines that whether the index can support the sorting order or not. The following example creates a compound index on the orderDate field (in ascending order) and the pincode field (in descending order.)


db.collection.createIndex( { orderDate: 1, pincode: -1 } )


A compound index cannot include a hashed index component.


[Related Blog: MongoDB vs Elasticsearch Comparison]


Create Indexes with Collation Specified


This operation has been added in version 3.4.


The collation is an option that allows users to specify rules on the basis of a particular language for comparison between the strings, such as rules for letter case and accent marks.


Syntax:


collation: {
   locale: <string>,
   caseLevel: <boolean>,
   caseFirst: <string>,
   strength: <int>,
   numericOrdering: <boolean>,
   alternate: <string>,
   maxVariable: <string>,
   backwards: <boolean>
}

Let’s have a look at the example to understand more:


db.collection.createIndex({
   name:1,
   formula:1,
   type:1
},
{
   collation:{
      locale:"en",
      strength:2
   }
});

Here in this above given example script, the strength attribute basically represents the comparison level of strings. You can then get case insensitive match with this query:


db.collection.find({name: "name"}).collation({locale: "en", strength: 2});


Here, we create an index named ‘name’. The index is created with the collation that denotes the comparison strength 2 and locale ‘en’.


The collation in indexes has a level of comparisons listed as:


  1. Primary level: At this level, the collation the operation performs the comparison on the basis of characters only while ignoring the difference of case or the diacritics (when written above or below a letter produces a difference in pronunciation).
  2. Secondary Level: The secondary level of comparison performs the operation over diacritics i.e. it performs the comparison between the primary differences and the diacritics. However, the base differences take priority over secondary differences.
  3. Tertiary level:  Tertiary level collation performs the comparison over the case or the letter variants i.e. it is a comparison between the base difference, the diacritics, and the case or letter variants (Tertiary level). This is the default level.
  4. Quaternary lever: The fourth level of difference performs the comparison for the limited specific case of letters such as the Japanese text.
  5. Identical level: It's kind of a tiebreaker and quite limited.

However, the collation can only be applied to the fields containing string values.


[Related Blog: MongoDB Query With Examples]


How to Find Indexes: getIndexes()


If we want to find an Index in MongoDB, we use the getIndex() method in MongoDB. 


Let’s understand this with the following example: db.collection.getIndexes()


the user needs to execute this command and if the operation executes successfully the output would be as shown in the figure:


The following output returns the result of the getIndex () command. As we can see in the output window, there are two indexes created in the following collection.


The one is the ‘_id’ field and the other is ‘Employeeid’ field. The value ‘1’ specifies that the indexes are created in ascending order. 


[Related Blog: MongoDB Create Database ]


How to Drop Indexes: dropindex()


For removing any specified index from a collection, we need to use the dropindex() method. 


Syntax: db.collection.dropIndex ()


The dropIndex() method uses the following parameter:


Parameter Type Description
index string or document

to drop any index the parameter Index is used to specify which of the indexes user need to drops.

If not used all the indexes from the collection will be deleted except ‘_’ indexes

To drop a text index, specify the index name.


In order to retrieve the name of the indexes, we can use the getIndex() method first. Then we can remove the particular field of the index using the dropIndex() method.


For example, if we want to Employeeid index from the collection:  db.Employee.dropIndex(Employeeid:1)



[Related Blog: MongoDB Commands Cheat Sheet]


The output of the following code will be as follows:


Output:


  1. The “nIndexeswas : 3”  states the number of indexes which were there before the dropIndex() operation was executed. Hence, it shows that there were 3 indexes in the collection.
  2. The ok: 1 output indicates that the query for dropping index was executed successfully, and the index “Employeeid” has been dropped from the index field.

[Related Blog: How To Install MongoDB On Windows]


If you want to drop all of the indexes altogether, you can use the indexes () method in order to achieve the task.


This execution can be done as follows:


db.Employee.dropIndex ()

If the command is executed successfully, the following Output will be shown:


Output:


  • As mentioned above,  the “nIndexeswas : 2” states the number of indexes which were there before the dropIndex() operation was executed. Hence, it shows that there were 3 indexes in the collection.
  • It is to be duly noted that each collection contains the ‘_id’ field which is also a kind of field value to the index i.e. by a default value. Therefore, this field will not be dropped by MongoDB and that is what the ‘msg’ tab stats.
  • The ok: 1 output indicates that the query for dropping indexes was executed successfully, and all the indexes from the collection have been dropped from the index field.

[Related Blog: Top 7 MongoDB GUI Tools In 2019]


Conclusion


By having a glance at this aforementioned article, we can simply conclude that creating indexes based on your queries is good for your database. Different types of indexes can be defined as per your collection. Not creating indexes may cause loss of time and slowing down the execution of queries. The query will scan for through the whole collection before executing and returning the appropriate result. This will also make your application very slow. 


However, creating a lot of indexes is also not good practice. If the database have a requirement of updating or inserting values frequently, then the indexes would also need to be update that many times. That’s why creating more than necessary indexes can cause extra time for the entire insert or update operations. Creating irrelevant Indexes can cause slow server speed and also affect your RAM spaces as the Indexes created are stored in RAM.


Let’s have a look at the  key takeaways from the Indexes in MongoDB topic:


  • Creating indexes for a collection can prove to be beneficial as they are important for efficient and faster searching of the document in a collection.
  • createIndex() method is used to create Indexes in a database. You can define any indexes depending on just one field values from the collection or on multiple field values from the collection. By default indexes are ordered in descending order, specify the key value to 1 for ascending order.
  • the getIndexes method is used to look for indexes in the collection. 
  • dropIndex() method is used to remove any specific index from the index field. However, if you want to remove all index the dropIndexes() method is used.
 
 
Explore MongoDB Sample Resumes! Download & Edit, Get Noticed by Top Employers!Download Now!
 
 

Subscribe For Free Demo

Free Demo for Corporate & Online Trainings.

Prasanthi
About The Author

Prasanthi is an expert writer in MongoDB, and has written for various reputable online and print publications. At present, she is working for Mindmajix, and writes content not only on MongoDB, but also on Sharepoint, Uipath, AWS, and Azure.


DMCA.com Protection Status

Close
Close