CODEWITHSUNDEEP
mongodbmongodb-query
Mansuro
Mansuro

Reputation: 4617

mongodb using less performant index

I am using mongodb 2.6, and I have the following query: 

db.getCollection('Jobs').find(
{ $and: [ { RunID: { $regex: ".*_0" } }, 
          { $or: [  { JobType: "TypeX" }, 
                    { JobType: "TypeY" }, 
                    { JobType: "TypeZ" }, 
                    { $and: [ { Info: { $regex: "Weekly.*" } }, { JobType: "YetAnotherType" } ] } ] } ] })

I have three different indexes: RunID, RunID + JobType, RunID + JobType + Info. Mongo is always using the index containing RunID only, although the other indexes seem more likely to produce faster results, it is even sometimes using an index consisting of RunID + StartTime while StartTime is not even in the list of used fields, any idea why is it choosing that index?

Upvotes: 4

Views: 68

Answers (2)

Mansuro
Mansuro

Reputation: 4617

Thanks to Sergiu's answer and Sammaye's comment, I think I found what I am looking for:

  1. I got rid of RunID index, since RunID is a prefix in many other indexes, mongodb will use it if it needs only RunID.

  2. Concerning $or, we have the following in the documentation:

When evaluating the clauses in the $or expression, MongoDB either performs a collection scan or, if all the clauses are supported by indexes, MongoDB performs index scans. That is, for MongoDB to use indexes to evaluate an $or expression, all the clauses in the $or expression must be supported by indexes. Otherwise, MongoDB will perform a collection scan.

As I mentioned earlier, RunID is already indexed, so we need a new index for the other fields in the query: JobType and Info, since JobType needs to be the index's prefix so that it can be used in queries not containing Info field, so the second index I created is 

{ "JobType": 1.0, "Info": 1.0}

As a result, mongodb will use a complex plan in which different indexes will be used.

Upvotes: 1

sergiuz
sergiuz

Reputation: 5529

Note1:

You can drop your first 2 indexes, RunID and RunID + JobType. It is enough to use just the expanded compound index RunID + JobType + Info; this can be also used to query on RunID or RunID + JobType fields, info here:

In addition to supporting queries that match on all the index fields, compound indexes can support queries that match on the prefix of the index fields.

When you drop those indexes, mongo will choose the only remained index.

Note2:

You can always use hint, to tell mongo to use a specific index:

db.getCollection('Jobs').find().hint({RunID:1, JobType:1, Info:1})

Upvotes: 2

Related Questions