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 easier in MongoDB.

If you want to enrich your career and become a professional in MongoDB, then visit Mindmajix - a global online training platform: "MongoDB Training" This course will help you to achieve excellence in this domain.

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. 

MongoDB Create Index - Table of Content

1. Importance of Indexes

2. Impact of Indexes

3. How to Create Indexes?

4. Options

5. Behaviors

6. How to Find Indexes

7. How to Drop Indexes

MongoDB Create Index

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 a Unique key also helps the user to avoid retrieving duplicate data

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. 

[Related Article: Difference Between Cassandra & MongoDB]

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 need 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.

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.

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.

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.

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.

Options for Collation

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

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.

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

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;

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");

Options for text Indexes

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

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 ( { <location field> : "2dsphere" } )

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 Article: Show Collections in MongoDB]

Options for 2d Indexes are:

Options for geoHaystack Indexes

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

Options for geoHayastack are:

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.

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 Article: Difference Between 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.

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( { <field1>: <type>, <field2>: <type2>, ... } )

In multiple field indexes, the sort order of the index plays a vital role. It determines 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.

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 Article: MongoDB Examples]

How to Find Indexes

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. 

How to Drop Indexes

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:

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 Article: 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 Article: 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 Article: Top 7 MongoDB GUI Tools In 2020]

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.