query performance on using single and multiple indexes in mongodb

Question

Hi I created indexes on collection using this

First:

db.device_data.createIndex({'device_id': 1});

Second:

db.device_data.createIndex({'device_id': 1,'slave_id':1});

Now when I do db.device_data.getIndexes() I get these

 {
        "v": 2,
        "key": {
            "_id": 1
        },
        "name": "_id_",
        "ns": "node-rest-auth-2.device_data"
    }, {
        "v": 2,
        "key": {
            "device_id": 1,
            "slave_id": 1
        },
        "name": "device_id_1_site_id_1_slave_id_1",
        "ns": "node-rest-auth-2.device_data",
        "background": true
    }, {

        "v": 2,
        "key": {
            "device_id": 1
        },
        "name": "device_id_1",
        "ns": "node-rest-auth-2.device_data"
    }

Can someone explain the second and third key objects. How is single index of device_id different from the index that has both device_id and slave_id.

Update Use Case

I have a total of 300,000 documents in a collection and I am trying to fetch 30,000 at one go. Without any index (except for _id), using MEAN stack it takes 20s, upon indexing I was expecting some speed up but there was no significant change.

glytching · Accepted Answer · 2017-09-21 10:07:27Z

The second index: device_id_1_site_id_1_slave_id_1 is a compound index, this index holds references to multiple fields. You might define an index like this if your common query use cases include both of these fields together. More details in the docs

The third index: device_id_1 is a single field index, this index holds a reference to a single field. You might define an index like this if your common query use cases include this field on its own. More details in the docs.

There may be some sub text missing from your question, perhaps something to explain why you are interested in the differences between single field and compound indexes. If this interest is purely theoretical then I'd suggest reading the docs to which I have linked above. If this interest is practical - perhaps you are investigating query performance issues - then I'd suggest updating the question to make these concerns clear so that any answers can address them..

Update 1: in answer to this comment: "What if the the query can have both multiple and signle"

At this point you might want to read up on index intersection in MongoDB.

More generally, when considering an indexing strategy you'll typically need to ...

Understand your read patterns: what attributes (and operators) are being used, what combinations of attributes are being used
Understand your non functional requirements; does your system have 'must meet' requirements for the elasped time of known read use cases
Understand the cost of indexes; they have some cost at write time and the incur a storage cost too

... and then start with the simplest indexing strategy and use profiling and/or explain to see how your queries behave and then tweak and retest etc.

Given the details you have supplied so far your starting position might be to drop this index: device_id_1 and to use profiling/explain to see if this index: device_id_1_site_id_1_slave_id_1 is sufficiently selective for your use cases. However, this suggestion is based on incomplete information since I know nothing about your non functional requirements, I know nothing about your tolerance for index costs etc. So, I'd suggest that you follow this approach

Understand your requirements
Understand MongoDB indexing (by reading the docs I have linked to above)
Test your system and use profiling/explain to understand what it is doing under the covers
Tweak and re-test
Repeat until you meet your requirements
Log metrics for read side performance so that you can keep an eye out for any degradation and be proactive about further tweaking to address performance degradation

What if the the query can have both multiple and signle. I should add three indexes. Two singles and One compound?
In this situation I'd definitely drop either the second or third index. Since MongoDB can make use of indexes where only the first key(s) are being used in the query, the index on both of the fields will serve even when only device_id is used in the query.

Collectives™ on Stack Overflow

query performance on using single and multiple indexes in mongodb

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related