Menu
say I have an Item document with :price and :qty fields. I sometimes want to find all documents matching a given :price AND :qty, and at other times it will be either :price on its own or :qty on its own.
I have already indexed the :price and :qty keys, but do I also need to create a compound index on both together or are the single key indexes enough?
Edit: I found this article on the mongodb site very useful:
http://www.mongodb.org/display/DOCS/Indexing+Advice+and+FAQ
505
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.
Indexes support the efficient execution of queries in MongoDB. Without indexes, MongoDB must perform a collection scan, i.e. scan every document in a collection, to select those documents that match the query statement.
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.
Multi-key indexes can be used when an index value occurs multiple times within a single document. For example, invoices might have invoice number, customer number, and customer name defined as the first three index fields, each occurring once within a given invoice.
An index on price helps with queries on price. An index on qty helps with queries on qty. An index on price and qty helps with queries on price AND queries on price and qty. So, it's like getting two indexes for the price of one. It will not help for queries on qty, though.
You should minimize the number of indexes you have, so drop one of your single-key indexes and create a compound index starting with that key.
As a general rule, if you're doing a query on x, y, and z, you should have an index like {x:1, y:1, z:1}. This index will make the following queries fast:
db.foo.find({x : ..., y : ..., z : ...}) db.foo.find({x : ..., y : ...}) db.foo.find({x : ...}) It will not make these queries fast:
db.foo.find({y : ..., z : ...}) db.foo.find({y : ...}) So, make sure your query has the starting key(s) of an index in it.
90
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
