Too Many Indexes May kill your DB performance

10 / Aug / 2016 by Pawan Goyal 2 comments

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

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

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

 

FOUND THIS USEFUL? SHARE IT

comments (2)

  1. Sudeep Dasgupta

    Compund Index does not work if the data in the field in different for around 50000, deletion and updation needs exact data, as I get tick data of price feed and its gets huge after six month so while deletion I have created index in segment and exchangetime and segment is not known as its has 50000 different data, but searching is fast as user provide exact segment and exchange time but while deletion of data of around 1 month I need to delete matching exchange between start and end date and its has compund index of segment and exchange so deletion is huge slow. I am working in mongo enterprise edition and even mongo support could not help this out. there is only one solution create a seperate index in exchangetime . So that means, indexes needs to created individually sometimes, compound index does not work all time

    Reply

Leave a Reply to Sudeep Dasgupta Cancel reply

Your email address will not be published. Required fields are marked *