How to index $ or sorted query

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 match one of these three conditions, and sort it by the latest. However, $ or queries do not use indexes in parallel when used with sorting. So how would you 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 will need more than one index for this query; There is no doubt about that.

The question is which indexes?

Now you should take into account that none of your $or will be able to sort your data in a radically optimal way using the index due to an error in the MongoDB query optimizer: https://jira.mongodb.org/browse/SERVER-1205 .

So, you know that $or will have some sort of performance problems with sorting, and that placing the sort field in the $or sentence indices is useless atm.

So, given that the first index you need is the one that covers the main query you make. Since @Leonid said you can make it a complex index, I would not do it the way he did it. Instead, I would do:

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

I am very not sure that the deleted field is in the index in general because of its ultra-low selectivity; it could actually create a less efficient operation (this is true for most databases, including SQL) indexed rather than retrieval. You will need this part for testing; perhaps the field should be the last (?).

Regarding the order of the index, I guessed again. I told DESC for all fields, because your view is DESC, but you need to explain it here.

So this should be able to handle the master clause of your request. Now consider those $or s.

Each $or will use the index separately, and the MongoDB query optimizer will look for indexes for them separately, as if they were separate queries at all, so something worth noting here is a small problem with compound indexes ( http: //docs.mongodb .org / manual / core / indexes / # compound-indexes ) is that they work with prefixes (example here: http://docs.mongodb.org/manual/core/indexes/#id5 ), so you don’t you can make one composite index for all three sentences, therefore a more optimal method of declaring indexes on $or (taking into account the error above):

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

He should be able to start searching for the optimal indexes for your query.

I must emphasize that you need to verify this yourself.

Answer 2

Mongodb can only use one index per request, so I see no way to use indexes for someid in your model.

So, the best approach is to add a special field for this task:

 ids = [creator._id, parent._id, somerelation._id] 

In this case, you can query without using the $or operator:

 db.things.find({ deleted: false, type: 'thing', ids: someid }).sort({ 'date.created': -1 }) 

In this case, your index will look something like this:

 {deleted:1, type:1, ids:1, 'date.created': -1} 

Answer 3

If you had the flexibility to configure the scheme, I would suggest adding a new field associated with it: [], which will contain creator._id, parent._id, some relation._id - you can update this field atomically when you update the main corresponding field, but now you can have a composite index in this field, type and created_date, which completely eliminates the need for $ or your request.

Answer 4

Given your indexing requirements, I would suggest you use the $ orderBy operator next to your $ or query. By this, I mean that you should be able to index the criteria in your $ or expressions used in your $ or query, and then you can $ orderBy sort the result.

For instance:

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

The above query will require composite indexes for each of the fields in $ or expressions in combination with the sorting object specified in the orderBy criteria.

eg:

 db.things.ensureIndex{'parent._id': 1,"date.created":-1} and so on for other fields. 

It is good practice to define a “limit” for the result, to prevent mongodb from doing a huge sort of memory. Read more about $ orderBy here.