CODEWITHSUNDEEP
mongodbindexing
d1mitar
d1mitar

Reputation: 226

MongoDB index suggestion

I have the following query:

a : true AND (b : 1 OR b : 2) AND ( c: null OR (c > startDate AND c <endDate))

So basically i am thinking of a compound index of all the three fields, because i have no sorting at all. At the first step, with the index on the boolean field, i will eliminate the largest portion of documents.

Then with the index on the second field, i saw that OR clause creates two separate queries and then combines them, while removing duplicates. So this should be pretty fast and efficient.

The last condition is a simple range of dates, so i think that adding the field to the index will be a good option.

Any suggestion on my thoughts? thanks

Upvotes: 0

Views: 377

Answers (1)

Sammaye
Sammaye

Reputation: 43884

This query:

a : true AND (b : 1 OR b : 2) AND ( c: null OR (c > startDate AND c <endDate))

could otherwise be translated as:

db.collection.find({
    a:true, 
    b:{$in:[1,2]}, 
    $or: [
        {c:null}, 
        {c: {$gt: startDate, $lt: endDate}}
    ]
})

Because of that $or you will most likely need two indexes, however, since the $or covers only c then you only need an index on c. So that our first index:

db.collection.ensureIndex({c:1})

Now we cannot use the $or with a compound index because compound indexes work upon a prefix manner and $ors are evaluated as completely separate queries for each clause, as such it would be best to use a,b as the prefix to our index here.

This means you just need an index to cover the other part of your query:

db.collection.ensureIndex({b:1,a:1})

We put b first due to the boolean value of a, our index should perform better with b first.

Note: I am unsure about an index on a at all due to its low cardinality.

Upvotes: 1

Related Questions