Menu
MongoDB seems like it is using an inefficient query pattern when one index is a subset of another index.
class Model
field :status, :type => Integer
field :title, :type => String
field :subtitle, :type => String
field :rating, :type => Float
index([
[:status, Mongo::ASCENDING],
[:title, Mongo::ASCENDING],
[:subtitle, Mongo::ASCENDING],
[:rating, Mongo::DESCENDING]
])
index([
[:status, Mongo::ASCENDING],
[:title, Mongo::ASCENDING],
[:rating, Mongo::DESCENDING]
])
end
The first index is being used both when querying on status, title and subtitle and sorting on rating and when querying on just status and title and sorting on rating even though using explain() along with hint() in the javascript console states that using the second index is 4 times faster.
How can I tell Mongoid to tell MongoDB to use the second index?
604
MongoDB uses multikey indexes to index the content stored in arrays. If you index a field that holds an array value, MongoDB creates separate index entries for every element of the array. These multikey indexes allow queries to select documents that contain arrays by matching on element or elements of the arrays.
MongoDB provides a method called createIndex() that allows user to create an index. Syntax – db.COLLECTION_NAME.createIndex({KEY:1}) The key determines the field on the basis of which you want to create an index and 1 (or -1) determines the order in which these indexes will be arranged(ascending or descending).
MongoDB may use multiple indexes to support a sort operation if the sort uses the same indexes as the query predicate. If MongoDB cannot use an index or indexes to obtain the sort order, MongoDB must perform a blocking sort operation on the data.
MongoDB can use the intersection of multiple indexes to fulfill queries. In general, each index intersection involves two indexes; however, MongoDB can employ multiple/nested index intersections to resolve a query.
You can pass options such as hint to Mongo::Collection using Mongoid::Criterion::Optional.extras
An example:
criteria = Model.where(:status => true, :title => 'hello world').desc(:rating)
criteria.extras(:hint => {:status => 1, :title => 1, :rating => -1})
extras accepts anything that Mongo::Collection can handle
110
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With