How to create index for sorting with $in query ?

[morgan.chengmo]

I have this kind of use case: one collection named products has info about products. There is one field tags which is array of string; and there is another field named create_time about creation time.

So, one document is like: 

{_id: xxx, tags: ['forsale', 'new'], create_time: '2013-06-01'}

Now, there is one application requirment to find all products with given set of tags and sorted by create_time. Intuitively, the query is like: 

db.products.find({tags: {$in: given_tags_array}}).sort({create_time: -1})

There are index created as:

db.products.ensureIndex({tags: 1})

db.products.ensureIndex({create_time: -1})

The performance of this query is not good. With the help of explain(), I found that query use index {create_time: -1} and scan a lot of documents. 

This seems a common scenario. query with array but sorting with another field. 

What's best practice to create index for this scenario?

Thanks,

-Morgan

[Asya Kamsky]

MongoDB can only use *one* index for a particular query, so you need to create a compound index.

db.products.ensureIndex({tags: 1, create_time:1}) should do it.

Asya