How compound indexes work in mongodb?

Question

I am using pymongo and mongoengine, Below is the collection schema.

class Person(Document, BaseMixin):
    school_id = StringField(required = True)
    first_name = StringField(default="")
    last_name = StringField(default="")
    email = StringField(default="")
    creation_time = DateTimeField(default = datetime.utcnow())
    user_status = StringField(default='active') # possible values, 'active', 'blocked', 'deleted'
    meta = {
        'indexes': [('school_id', 'first_name'), ('school_id', 'user_status', 'first_name'),
                    ('school_id', 'user_status', 'creation_time'), ('school_id', 'creation_time')],
        'index_background': True
    }

I want to get the list of active and blocked users for the particular date range and search active and blocked users by their first name. I was going through the mongo docs about the indexes and found this document stating its better to have indexes on status filed mongo docs

I have created the above indexes to test which index mongo picks while processing queries. The collection contains data for multiple schools so added index on school_id. First query: Index used = school_id_1_user_status_1_first_name_1

Person.objects(school_id = 'test', user_status__in = ['active', 'blocked'], first_name = 'test_name')

Second query: index used = school_id_1_creation_time_1

Person.objects(school_id = 'test', user_status__in = ['active', 'blocked'], creation_time__gte = datetime(2018, 7, 1))

So I am not sure how indexes are being used in this situation?, Can anyone give more information about it. Also, suggest me if I am doing something wrong with my indexing scheme or collection schema. Thanks.

score 0 · Answer 1 · answered Jul 28 '18 at 15:53

0

You actually have indexes what you don't really need. You can reduce your indexes to:

 'indexes': [('school_id', 'first_name', 'user_status'),('school_id',
 'creation_time','user_status')]

This because of field order of query is not essential, just that, that field exists at the query. So, even index has three fields, query can uses only two of those first fields. Rule is that first field of index MUST be at the query, so that query can use that index.

answered Jul 28 '18 at 15:53

JJussi

1,540
12
12

1

Hi @jjussi, I have added those index just to analyze which index mongo query uses. I am not sure how compound indexes work in mongo. According to your explanation, when both ('school_id', 'creation_time','user_status') and ('school_id', 'creation_time') indexes were present then given query should use the 3 field index ? but it is using the 2 fields index. so adding ('school_id', 'creation_time','user_status') will not be useful? So please explain how compounded index tree builds and searched. – Shubham Najardhane Aug 23 '18 at 09:28

How compound indexes work in mongodb?

1 Answers1