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.
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.
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]
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:
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.
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.
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.
|
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.
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: { <storage-engine-name>: <options> }
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.
|
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.
|
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.
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 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. |
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" } )
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 |
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]
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. |
A geoHaystack index is optimized in such a way that it helps in returning the results over small areas.
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. |
The behavior of the createIndex () method is mentioned below:
Examples:
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" }
}
db.records.createIndex( { score: 1 })
The following example creates an ascending index on the field score.
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.
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>
}
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’.
However, the collation can only be applied to the fields containing string values.
[Related Article: MongoDB Examples]
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.
For removing any specified index from a collection, we need to use the dropindex() method.
Syntax:
db.collection.dropIndex ()
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)
The output of the following code will be as follows:
Output:
[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.
db.Employee.dropIndex ()
If the command is executed successfully, the following Output will be shown:
Output:
[Related Article: Top 7 MongoDB GUI Tools In 2020]
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.
Name | Dates | |
---|---|---|
MongoDB Training | Oct 08 to Oct 23 | View Details |
MongoDB Training | Oct 12 to Oct 27 | View Details |
MongoDB Training | Oct 15 to Oct 30 | View Details |
MongoDB Training | Oct 19 to Nov 03 | View Details |
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, and AWS.