CODEWITHSUNDEEP
mongodbindexingpymongo
professrr
professrr

Reputation: 105

How to optimize big query with compond index?

I am trying to reach the best execution time of the query, by passing the best compound index. So the query is:

db.BMW_offers
   .find({"model":"X5", "predict_value":{"$gt":700,"$lte":1300}, "owners_number":{"$lte":2}, "status":{"$ne":"ERROR"}, "year":{"$gt":2010 }})
   .sort({"offer_creation_date":-1, "predict_value":1, "mileage": 1})

Following this ESR rule in Mongo I've created the following comound index:

db[col_name].create_index([
            ('model', 1),                # Equality first -> (model: "X5")
            ('offer_creation_date', -1), # Sort second -> (offer_creation_date: -1)
            ('predict_value', 1),        # Sort second -> (predict_value: 1)
            ('mileage', 1),              # Sort second -> (mileage: 1)
            ('predict_value', -1),       # Range third -> (predict_value: {$gt: 700})
            ('predict_value', 1),        # Range third -> (predict_value: {$lte: 1300})
            ('owners_number', 1),        # Range third -> (owners_number: {$lte: 2})
            ('status', 1),               # Range third -> (status: {$ne: "ERROR"})
            ('year', -1)                 # Range third -> (year: {$gt: 2010})
        ], name='actual_search', default_language='english')

Is it the best index for this query?

Upvotes: 1

Views: 150

Answers (1)

Tomov Nenad
Tomov Nenad

Reputation: 164

Yes it is, you covered all fields from filtering stage and you also covered fields from sorting stage with right direction.

Upvotes: 2

Related Questions