How to index an $or query with sort

Question

Suppose I have a query that looks something like this:

db.things.find({
  deleted: false,
  type: 'thing',
  $or: [{
    'creator._id': someid
  }, {
    'parent._id': someid
  }, {
    'somerelation._id': someid
  }]
}).sort({
  'date.created': -1
})

That is, I want to find documents that meets one of those three conditions and sort it by newest. However, $or queries do not use indexes in parallel when used with a sort. Thus, how would I index this query?

http://docs.mongodb.org/manual/core/indexes/#index-behaviors-and-limitations

You can assume the following selectivity:

• deleted - 99%
• type - 25%
• creator._id, parent._id, somerelation._id - < 1%

Answer 1

Now you are going to need more than one index for this query; there is no doubt about that.

The question is what indexes?

Now you have to take into consideration that none of your $ors will be able to sort their data cardinally in an optimal manner using the index due to a bug in MongoDBs query optimizer: https://jira.mongodb.org/browse/SERVER-1205 .

So you know that the $or will have some performance problems with a sort and that putting the sort field into the $or clause indexes is useless atm.

So considering this the first index you want is one that covers the base query you are making. As @Leonid said you could make this into a compound index, however, I would not do it the order he has done it. Instead, I would do:

db.col.ensureIndex({type:-1,deleted:-1,date.created:-1})

I am very unsure about the deleted field being in the index at all due to its super low selectivity; it could, in fact, create a less performant operation (this is true for most databases including SQL) being in the index rather than being taken out. This part will need testing by you; maybe the field should be last (?).

As to the order of the index, again I have just guessed. I have said DESC for all fields because your sort is DESC, but you will need to explain this yourself here.

So that should be able to handle the master clause of your query. Now to deal with those $ors.

Each $or will use an index separately, and the MongoDB query optimizer will look for indexes for them separately too as though they are separate queries altogether, so something worth noting here is a little snag about compound indexes ( http://docs.mongodb.org/manual/core/indexes/#compound-indexes ) is that they work upon prefixes ( an example note here: http://docs.mongodb.org/manual/core/indexes/#id5 ) so you can't make one single compound index to cover all three clauses, so a more optimal method of declaring indexes on the $or (considering the bug above) is:

db.col.ensureindex({creator._id:1});
db.col.ensureindex({aprent._id:1});
db.col.ensureindex({somrelation._id:1});

It should be able to get you started on making optimal indexes for your query.

I should stress however that you need to test this yourself.