Introduction to mongoDB Index

Description

Here in this article let’s explain why we need a database index, by going through a very simple example. Suppose that we have a database table called Employee with three columns – Employee_Name, Employee_Age, and Employee_Address. Assume that the Employee table has thousands of rows.

Now, let’s say that we want to run a query to find all the details of any employees who are named ‘Alex’?

Once we run a query to achieve the above result, what exactly goes on behind the scenes is, to find employees who are named Alex. The database would literally have to look at every single row in the Employee table to see if the Employee_Name for that row is ‘Alex’. So, every row up until the last row must be searched – which means thousands of rows in this scenario will have to be examined by the database to find the rows with the name ‘Alex’.

It’s almost like looking through the entire table with the human eye – this process is very slow and not easy, that is why indexes can help a great deal.

The whole point of having an index is to speed up search queries by essentially cutting down the number of records/rows in a table that needs to be examined.

An index is a data structure (most commonly a B- tree) that stores the values for a specific column in a table. An index is created on a column of a table. So, the key points to remember that, an index consists of column values from one table, and that, those values are stored in a data structure.

Here is the pictorial presentation of an index table.

Here in the above example, suppose we have a table 'player' of two columns - 'name' and 'age' and we have shown the location of rows by 'row' column, but point to be noted that 'row' is not a created column in the table.

On the left, each index entry contains the index key (Name). Each entry also includes a reference (which points) to the rows of the table player, which share that particular value and from which we can retrieve the required information.

If we want to see those rows which contain the name 'Ramel', the pointer will point to the table player in location 4,6,8 and 1000 which is referring from the index table.

Index Types

MongoDB provides different types of an index to support specific types of data and queries.

Default _id

All the collections of MongoDB have an index on the _id field that exists by default. If no values specified for the _id the mongodb will create an _id field with an ObjectId value.

The _id index is unique, it restricts clients from inserting two documents with the same value for the _id field.

Single Field

MongoDB supports user-defined indexes on a single field of a document. Here is the example below considering a single column index.

db.userdetails.find().sort({"education":1})

The above example shows the "education" column from the collection userdetails is sorting in ascending order.

Compound Index

MongoDB also supports user-defined indexes on multiple fields. The order of fields listed in a compound index has significance. For instance, if a compound index consists of {"education": 1, "password": -1 }, the index sorts first by education  and then, within each education value, sort by password. Consider the following illustration of this compound index:

db.userdetails.find().sort({"education":1,"password":-1})

Multikey Index

MongoDB uses multikey indexes to index the content stored in arrays. When you index on a column that holds an array value, MongoDB creates separate index entries for every element of the array. These multikey indexes allow queries to select documents that contain arrays by matching on element or elements of the arrays.

Here is a sample document within a collection :

{
        "_id" : ObjectId("528f34950fe5e6467e58ae77"),
        "user_id" : "user1",
        "password" : "1a2b3c",
        "sex" : "Male",
        "age" : 17,
        "date_of_join" : "16/10/2010",
        "education" : "M.C.A.",
        "profession" : "CONSULTANT",
        "interest" : "MUSIC",
        "extra" : {
                <span class="style1">"friends"</span> : {
                        "<span class="style2">valued_friends_id</span>" : [
                                "kumar",
                                "harry",
                                "anand"
                        ],
                        <span class="style2">"ban_friends_id</span>" : [
                                "Amir",
                                "Raja",
                                "mont"
                        ]
                }
        }
}

Consider the following illustration of a multikey index:

db.userdetails.find().sort({"extra.friends.ban_friends_id":1})

Here in the above example the 'extra' is an array and 'friends' and 'ban_firends_id' are subarray.

Geospatial Index

To support efficient queries of geospatial coordinate data, MongoDB provides two special indexes: 2d indexes and 2sphere indexes uses for planar geometry when returning results spherical geometry to return results.

Text Indexes

Another type of index that MongoDB provides is text index, that supports searching for string content in a collection. These text indexes do not store language-specific stop words (e.g. "the", "a", "or") and restrict the words in a collection to only store root words.

Hashed Indexes

To support hash-based sharding, MongoDB provides a hashed index type, which indexes the hash of the value of a field. These indexes have a more random distribution of values along with their range, but only support equality matches and cannot support range-based queries.

Index Properties

Unique Indexes

The unique property for an index causes reject duplicate values for the indexed field. To create a unique index on a field that already has duplicate values. Other than the unique constraint, unique indexes are functionally interchangeable with other MongoDB indexes.

Sparse Indexes

The sparse property of an index ensures that the index only contains entries for documents that have the indexed field and skips documents that do not have the indexed field.

The sparse index option can be combined with the unique index option to reject documents that have duplicate values for a field but ignore that do not have the indexed key.

TTL Indexes

Another special index is TTL indexes that can be used to automatically remove documents from a collection after a certain amount of time. This is ideal for certain types of information like machine generated event data, logs, and session information that only need to stay behind in a database for a finite amount of time.

Previous: MongoDB DELETE
Next: MongoDB Single Field Index