MongoDB Indexes


What is an Index?

The indexes are the data structure that stores the collection's data set and filed values in an easy to pass-through and also stores files in the special order either Ascending or Descending and MongoDB indexes are similar to the other indexes in any of the databases.

Indexes are very important in any database.  For example, if you have a collection with thousands of documents and with no indexes on it, and then each query to filter documents or to find specific documents then MongoDB would need to scan the whole collection to find those documents that will surely degrade the query performance and if the collection has indexes, MongoDB would use those indexes to reduce the number of documents to be scanned in the collection.

MongoDB creates a default unique index on the "_id" filed whenever we create a collection and you can't drop the index.

The benefits of using indexes?

  • It supports the efficient execution of any queries. Without indexes, for each query, MongoDB must scan the whole collection Scan (COLLSCAN) and with indexes, MongoDB performs an index scan (IXSCAN).
  • It improves the MongoDB query performance for find method and aggregate method.  

A drawback of having indexes?

We know indexes are better for any query performance. However, having too many indexes can slow down other operations like Insert, Update, and Delete operations.


Indexing Strategies

Creating an index is easy. But, the finest indexes for your application must take a number of attention/concerns into the conclusion like what types of queries you expect to access the collection, and the ratio of reads vs writes, those can be decided only with a deep understanding of the application's queries and the most important area is the amount of free memory on your system. Because for the fastest query processing we must ensure indexes fit in RAM so that transaction/system can avoid reading the indexes from the disk.

Types of indexes in MongoDB?

  • Default _id
  • Single Field
  • Compound Index
  • Multikey Index
  • Geospatial Index
  • Text Index
  • Hashed Index

Default _id

MongoDB creates a default unique index on the "_id" filed whenever we create a collection and you can't drop the index.

--- Create a document ---

db.employee.insertMany([
{empno:100, empname:"Rehan Khan", dept:"IT", location:"Bangalore"},
{empno:101, empname:"Fiza Khan", dept:"IT", location:"Bangalore"},
{empno:102, empname:"Umer Mohd", dept:"IT", location:"Hyderabad"},
{empno:103, empname:"Mohd zuber", dept:"Marketing", location:"Hyderabad"}
]);

-- list the default index details which have created by MongoDB automatically --

 Example: db.employee.getIndexes()

MongoDB > db.employee.getIndexes()
[
        {
                "v" : 2,
                "key" : {
                        "_id" : 1
                },
                "name" : "_id_",        ----------------------- Index name (default)
                "ns" : "mydb.employee"
        }
]


Single Filed Index

 on top of the default _id index, MongoDB allows the creation of user-defined ascending or descending order indexes.

Create an Ascending index on a single field

--- Create a document ---

db.employee.insertMany([
{empno:100, empname:"Rehan Khan", dept:"IT", location:"Bangalore"},
{empno:101, empname:"Fiza Khan", dept:"IT", location:"Bangalore"},
{empno:102, empname:"Umer Mohd", dept:"IT", location:"Hyderabad"},
{empno:103, empname:"Mohd zuber", dept:"Marketing", location:"Hyderabad"}
]);

-- Creating an ascending index on the "empno" field of the employee collection --

Example: db.employee.createIndex( { empno: 1 } )

Output:

MongoDB > db.employee.createIndex( { empno: 1 } )
{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 1,
        "numIndexesAfter" : 2,
        "ok" : 1
}


Note: The value of "1" shows it's an Ascending and value of "-1" shows a descending.

-- list the index details which have created on employee collection --

Example: db.employee.getIndexes()

Output:

MongoDB > db.employee.getIndexes()
[
        {
                "v" : 2,
                "key" : {
                        "_id" : 1 
                },
                "name" : "_id_",
                "ns" : "mydb.employee"
        },
        {
                "v" : 2,
                "key" : {
                        "empno" : 1   ----------------------- "1" shows that it's an Ascending 
                },
                "name" : "empno_1",   ----------------------- Index name (Ascending) 
                "ns" : "mydb.employee"
        }
]


Create a descending index on a single field

-- creating an ascending index on the "empno" field of the employee collection --

Example: db.employee.createIndex( { empno: -1 } )

Output:

MongoDB > db.employee.createIndex( { empno: -1 } )
{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 2,
        "numIndexesAfter" : 3,
        "ok" : 1
}


-- list the index details which have created on employee collection --

Example: db.employee.getIndexes()

Output:

MongoDB > db.employee.getIndexes()
[
        {
                "v" : 2,
                "key" : {
                        "_id" : 1
                },
                "name" : "_id_",
                "ns" : "mydb.employee"
        },
        {
                "v" : 2,
                "key" : {
                        "empno" : -1   ----------------------- "-1" shows that it's a descending
                },
                "name" : "empno_-1",   ----------------------- Index name (descending) 
                "ns" : "mydb.employee"
        }
]


Create an index on an Embedded document 


-- create an embedded document --

db.employee_details.insertMany([
{empno:100, empname:"Rehan Khan", dept:"IT", location:{state:"TS" ,city:"Bangalore"}},
{empno:101, empname:"Fiza Khan", dept:"IT", location:{state:"KA" ,city:"Bangalore"}},
{empno:102, empname:"Umer Mohd", dept:"IT", location:{state:"KA" ,city:"Bangalore"}},
{empno:103, empname:"Mohd zuber", dept:"Marketing", location:{state:"TS" ,city:"Hyderabad"}}
]);


-- Creating an ascending index on the "location" field of the employee_details collection --


Example: db.employee_details.createIndex( { location: 1 } )

Output:

MongoDB > db.employee_details.createIndex( { location: 1 } )
{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 1,
        "numIndexesAfter" : 2,
        "ok" : 1
}


-- List the index details which have created on embedded document of employee_details collection --

Example: db.employee_details.getIndexes()

Output:

MongoDB > db.employee_details.getIndexes()
[
        {
                "v" : 2,
                "key" : {
                        "_id" : 1
                },
                "name" : "_id_",
                "ns" : "mydb.employee_details"
        },
        {
                "v" : 2,
                "key" : {
                        "location" : 1
                },
                "name" : "location_1",         ----------------------- Index name (ascending)
                "ns" : "mydb.employee_details"
        }
]


Compound Index

 It is an index that contains references to multiple fields within a document. Compound indexes support queries that match on multiple fields which are part of the compound index.


Note: You can't create compound indexes on the hashed index type. you will receive an error if you try to create it. The order of the fields added to the compound index is important.

We use compound indexes when we use multiple fields in the query to benefited faster results and performance.

-- Create a document ---

db.employee.insertMany([
{empno:100, empname:"Rehan Khan", dept:"IT", location:"Bangalore"},
{empno:101, empname:"Fiza Khan", dept:"IT", location:"Bangalore"},
{empno:102, empname:"Umer Mohd", dept:"IT", location:"Hyderabad"},
{empno:103, empname:"Mohd zuber", dept:"Marketing", location:"Hyderabad"}
]);


-- Creating a compound index on the "empno and ename" fields of the employee collection --

 Example: db.employee.createIndex({"empno":1,"empname":1})

Output:

MongoDB > db.employee.createIndex({"empno":1,"empname":1})
{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 1,
        "numIndexesAfter" : 2,
        "ok" : 1

}


-- List the index details which have created a compound index of employee collection --

 Example: db.employee.getIndexes()

 Output:

MongoDB Enterprise > db.employee.getIndexes()
[
        {
                "v" : 2,
                "key" : {
                        "_id" : 1
                },
                "name" : "_id_",
                "ns" : "mydb.employee"
        },
        {
                "v" : 2,
                "key" : {   
                        "empno" : 1,
                        "empname" : 1 
                },
                "name" : "empno_1_empname_1",  ------------- compound index
                "ns" : "mydb.employee"
        }

]
Multikey Index

 To index a field that holds an array value. MongoDB automatically creates a multikey index if any indexed filed is an array; you don't need to explicitly define the multikey type.

 -- create a document with multi-key fields --

db.employee_hobbies.insertMany([
{empno:100, empname:"Rehan Khan", dept:"IT",  hobbies:["Cricket","Footbal","cycling","reading"]},
{empno:101, empname:"Fiza Khan", dept:"IT", hobbies:["Cricket","Footbal","cycling","reading"]},
{empno:102, empname:"Umer Mohd", dept:"IT", hobbies:["Cricket","Footbal","cycling","reading"]},
{empno:103, empname:"Mohd zuber", dept:"Marketing", hobbies:["Cricket","Footbal","cycling","reading"]}
]);


-- Creating a multikey index on the "hobbies"  fields of the employee_hobbies collection --

Example: db.employee_hobbies.createIndex({"hobbies":1})

Output:

MongoDB Enterprise > db.employee_hobbies.createIndex({"hobbies":1})
{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 1,
        "numIndexesAfter" : 2,
        "ok" : 1
}


-- List the index details which have created a multifield key index on employee_hobbies collection --


Example: db.employee_hobbies.getIndexes()


Output:

MongoDB Enterprise > db.employee_hobbies.getIndexes()
[
        {
                "v" : 2,
                "key" : {
                        "_id" : 1
                },
                "name" : "_id_",
                "ns" : "mydb.employee_hobbies"
        },
        {
                "v" : 2,
                "key" : {
                        "hobbies" : 1    
                },
                "name" : "hobbies_1",           --------------- Multikey Index
                "ns" : "mydb.employee_hobbies"
        }
]


Geospatial Index

Geospatial Indexes are used to coordinate geospatial data.

To find the location of any from your current location, you need to have a Geospatial index and each in two dimensions i.e, Longitute and Latitude.

The creation of Geospatial Index is the same as using the createIndex() function but with additional values like "2d" or "2dsphere" instead of 1 or -1.


Storing Geospatial data

 point: this is a basic and common type of data and it's used to present only one specific point on the grid.

 --- to create a collection with single point on the grid ---

 db.location_point.insertOne({"name": "Big Basket", "location": {"coordinates": [-0.1268194, 51.5007292],"type": "Point"}});

 Output

 MongoDB  > db.location_point.find().pretty()
{
        "_id" : ObjectId("5f02e54a3954901e8c0f1e9e"),
        "name" : "Big Basket",
        "location" : {
                "coordinates" : [
                        -0.1268194,
                        51.5007292
                ],
                "type" : "Point"    ----------------- point type Geospatial 
        }

}
Polygon: it's a little complex than the single point type 

 --- to create a collection with polygon type ---

db.location_polygon.insertOne({"name": "D-Mart","location": {"coordinates": [[[-0.159381, 51.513126],[-0.189615, 51.509928],[-0.187373, 51.502442],[-0.153019, 51.503464],[-0.159381, 51.513126]]], "type": "Polygon"}});

Output

MongoDB > db.location_polygon.find().pretty()
{
        "_id" : ObjectId("5f02e5363954901e8c0f1e9d"),
        "name" : "D-Mart",
        "location" : {
                "coordinates" : [
                        [
                                [
                                        -0.159381,
                                        51.513126
                                ],
                                [
                                        -0.189615,
                                        51.509928
                                ],
                                [
                                        -0.187373,
                                        51.502442
                                ],
                                [
                                        -0.153019,
                                        51.503464
                                ],
                                [
                                        -0.159381,
                                        51.513126
                                ]
                        ]
                ],
                "type" : "Polygon"   ----------- Polygon type Geospatial 
        }
}

Geospatial Indexing

 To query Geospatial data, you initially need to create a Geospatial index. We basically have two options "2d" and "2dsphere".


-- 2d Geospatial Index --

 The 2d index supports queries that calculate geometric on a 2-dimensional plane.

 --- create a 2d Geospatial index ---

 db.location_point.createIndex({coordinates:"2d"})
Example:

MongoDB > db.location_point.createIndex({coordinates:"2d"})
{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 1,
        "numIndexesAfter" : 2,
        "ok" : 1
}

Output

 MongoDB Enterprise > db.location_point.getIndexes()
[
        {
                "v" : 2,
                "key" : {
                        "_id" : 1
                },
                "name" : "_id_",
                "ns" : "mydb.location_point"
        },
        {
                "v" : 2,
                "key" : {
                        "coordinates" : "2d"    ---------------- 2d Geospatial type
                },
                "name" : "coordinates_2d",  ---------------- 2d Geospatial Index
                "ns" : "mydb.location_point"
        }

]


-- 2dsphere Geospatial Index -- 

 2dsphere indexes support queries that calculate Geometric like a sphere. 

 --- create a 2dsphere Geospatial index ---


db.location_polygon.createIndex({coordinates:"2dsphere"})


Example

 MongoDB > db.location_polygon.createIndex({coordinates:"2dsphere"})
{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 1,
        "numIndexesAfter" : 2,
        "ok" : 1

}

Output

MongoDB Enterprise > db.location_polygon.getIndexes()
[
        {
                "v" : 2,
                "key" : {
                        "_id" : 1
                },
                "name" : "_id_",
                "ns" : "mydb.location_polygon"
        },
        {
                "v" : 2,
                "key" : {
                        "coordinates" : "2dsphere"   --------------- 2dsphere Geospatial  type
                },
                "name" : "coordinates_2dsphere", --------------- 2dsphere Geospatial Index
                "ns" : "mydb.location_polygon",
                "2dsphereIndexVersion" : 3
        }
]


Text Index

Comments

Post a Comment

Popular posts from this blog

Auditing in Oracle database

Unified Auditing: Oracle Database 12c Release 1 introduces a new auditing feature called Unified Auditing (12.1). Oracle Unified Auditing modifies the database's fundamental auditing capabilities. In earlier versions of Oracle, each component has its own audit trail. With unified auditing, all auditing is compiled into a single repository and view. Due to the fact that all audit data is now in a single location and a single format, there is a double simplification as a result. This data is made available in a consistent format in the UNIFIED AUDIT TRAIL data dictionary view, which is made possible by the unified audit trail, which is stored in a read-only table in the AUDSYS schema in the SYSAUX tablespace.  DBAs can build audit rules and then assign them to various users using Oracle 12c Unified Auditing, which has the same advantages as giving users varying system capabilities based on their responsibilities. The unified_audit_trail database will contain all of the audit records....
Read more

rs.stepDown() in MongoDB replication

It instructs the primary of the replica set to become a secondary. After the primary steps down,the eligible secondaries will hold an election for primary. Generally, the rs.stepDown() does not immediately step down the primary. If no electable secondaries are up to date with the primary, the primary waits up to secondaryCatchUpPeriodSecs (by default 10 seconds)  for a secondary to catch up. Once an electable secondary is available, the method steps down the priamry.  Once stepped down, the original primary becomes a secondary and is ineligible from becoming primary again for the remainder of time specified by stepDownSecs. The rs.stepDown() method attempts to terminate long-running user's operations that block the primary from stepping down, such as an index build, a write operation, or a map-reduce job. Syntax: rs.stepDown(stepDownSecs, secondaryCatchUpPeriodSecs)
Read more

Tuples in Python

 Tuples Basically Tuples in Python are very similar to Lists, but, Tuples are immutable which means that they can not be modified/changed. We generally use Tuples to make sure the values are not changed like, dates, Year, and days of the week or dates on a Calendar.  Creating a Tuple: >>> my_t1=(1,2,3) >>> my_t2=('one','two','three') >>> my_t1 (1, 2, 3) >>> my_t2 ('one', 'two', 'three') -- check the length of tupple: >>> len(my_t1) 3 >>> len(my_t2) 3 -- Tuples can have mixed objects as like Lists. >>> my_t3=(1,'two',3,'four') >>> my_t3 (1, 'two', 3, 'four') -- indexing with tuples  >>> my_t3=(1,'two',3,'four') >>> my_t3[2] 3 Immutable Tuples We can not modify the value. Here is the example: >>> my_t3=(1,'two',3,'four') >>> my_t3[2]=4 Traceback (most recent call last):  ...
Read more