0

In mongo, When creating an index I am trying to figure out whether the following query would have an index on a) category_ids and status, OR b) category_ids, status and name???

Source.where(category_ids: [1,2,3], status: Status::ACTIVE).order_by(:name) # ((Ruby/Mongoid code))

Essentially, I am trying to figure out whether indexes should include the ORDER_BY columns? or only the WHERE clauses? Where could I read some more about this?

Kamilski81
  • 14,409
  • 33
  • 108
  • 161
  • 2
    Yes an index on this particular query would be more beneficial having both the query fields and the sort field in index. Here are afew good links: http://docs.mongodb.org/manual/applications/indexes/ http://docs.mongodb.org/manual/faq/indexes/#how-do-you-determine-what-fields-to-index http://jasonwilder.com/blog/2012/02/08/optimizing-mongodb-indexes/ http://stackoverflow.com/questions/10329104/why-does-direction-of-index-matter-in-mongodb http://www.slideshare.net/kbanker/mongo-indexoptimizationprimer from a quick google search – Sammaye Feb 11 '13 at 15:03

1 Answers1

3

Yes, an index on thius particular query would be beneficial to the speed of the query. However there is one caveat here, the order of the index fields.

I have noticed you are using an $in there on category_ids. This link is particularly useful in understanding a little complexity which exists from using an $in with an index on the sort (or a sort in general in fact): http://blog.mongolab.com/2012/06/cardinal-ins/

Towards the end it gives you an indea of an optimal index order for your type of query:

The order of fields in an index should be:

First, fields on which you will query for exact values.
Second, fields on which you will sort.
Finally, fields on which you will query for a range of values.

For reference a couple of other helpful links are as follows:

These will help you get started on optimising your indexes and making them work for your queries.

Community
  • 1
  • 1
Sammaye
  • 43,242
  • 7
  • 104
  • 146