Too Many Indexes May kill your DB performance

Everyone thinks of creating index, but nobody pays attention towards removing them once they are created and not required .Too many indexes or unnecessary index can degrade your DB performance.

To Optimize indexes in mongo we need to understand following things :

• How indexes works in mongoDB:

Let’s say we have created compound index on name and email on user collection.

db.Users.ensureIndex({“name”:1, “email”:1,”status”:1})

So above index will be relevant and helpful in three types of queries:

1. Queries matching ‘name’
2. Queries matching name and email in that order
3. Queries matching name,  email and status in that order

*** Queries matching Email, will not use this index.

Read more about mongodb Indexes

2. How to check indexes on collection in mongoDB:

First check the indexes and size of indexes on collection by running following command.

Output

[js]db.getCollection(‘Users’).stats();

{

"ns" : "ecom.Users",

"count" : 159023,

"size" : 37527952,

"avgObjSize" : 235,

"numExtents" : 9,

"storageSize" : 58441728,

"lastExtentSize" : 20643840.0,

"paddingFactor" : 1.0,

"paddingFactorNote" : "paddingFactor is unused and unmaintained in 3.0. It remains hard coded to 1.0 for compatibility only.",

"userFlags" : 1,

"capped" : false,

"nindexes" : 5,

"totalIndexSize" : 36922816,

"indexSizes" : {

"_id_" : 5240816,

"oldId_1" : 5028240,

"name_1" : 8543920,

"name_1_email_1" : 10955840,

"createdBy.id_1" : 7154000

},

"ok" : 1.0

}

[/js]

Here we can see that there is unnecessary  index on “name”

because compound index on name and email are already there.

If we have index on name and email then separate index on “name” is not required.

Removing the index on name will reduce the size of index and read and write will be faster.

3. How to remove/delete index in mongoDB:

To drop indexes in mongo use following command

[js]db.collection.dropIndex(<b>index</b>).

db.Users.dropIndex( "name_1" )

[/js]

By following above mentioned points, we can ensure our application’s performance by optimizing our DB indexes.

I hope this will be helpful. Thanks.