About mongodb index

Question

i have one question about mongodb index. Suppose we have two models:

class Book
  include Mongoid::Document

  field :user_id
  field :borrower_id

  belongs_to :user
end

class User
  include Mongoid::Document

  has_many :books
end

Question

If i wonner find some books:

current_user.books.roder_by(:created_at.desc)

which index should i create for Book

index({user_id: 1, created_at: -1})

or

index({user_id: 1})
index({created_at: -1)

and why?

The compound index since MongoDB cannot use index intersectionijng for sort and find, separately as such the latter two indexes would be sup-optimal to answer the query. Of course index intersecioning would be sub-optimal anyway — Sammaye, Jan 15 '15 at 09:07

score 0 · Answer 1 · answered Jan 15 '15 at 11:52

I would go for the compound index due to a number of limitations within MongoDBs (new) index intersectioning which means that two indexes cannot be used separately for find and sort of a query: http://docs.mongodb.org/manual/core/index-intersection/#index-intersection-and-sort as such in this case two separate indexes are not optimal for answering your query, only one index will be used, most likel it will be a in-memory sort limited to 32MB of RAM.

It is also good to note that ndex intersectioning is a last resort at all times. You should only do it if you cannot performantly create a compound index to cover the query since intersectioning is very expensive to begin with.

About mongodb index

Question

1 Answers1