Databases Reference
In-Depth Information
If we use an index for this type of query, we'd end up looking through most of the index
tree, loading, say, 60GB of the index into memory. Then we'd have to follow all of the
pointers in the index, loading 230GB of data from the collection. We'd end up loading
230GB + 60GB = 290GB—more than if we hadn't used an index at all!
Thus, indexes are generally most useful when you have a small subset of the total data
that you want returned. A good rule of thumb is that they stop being useful once you
are returning approximately half of the data in a collection.
If you have an index on a field but you're doing a large query that would be less efficient
using that index, you can force MongoDB not to use an index by sorting by
{"$natu
ral" : 1}
. This sort means “return data in the order it appears on disk,” which forces
MongoDB to not use an index:
> db.foo.find().sort({"$natural" : 1})
If a query does not use an index, MongoDB does a
table scan
, which means it looks
through all of the documents in the collection to find the results.
Write speed
Every time a new record is added, removed, or updated, every index affected by the
change must be updated. Suppose you insert a document. For each index, MongoDB
has to find where the new document's value falls on the index's tree and then insert it
there. For deletes, it must find and remove an entry from the tree. For updates, it might
add a new index entry like an insert, remove an entry like a delete, or have to do both
if the value changes. Thus, indexes can add quite a lot of overhead to writes.
Tip #24: Create indexes that cover your queries
If we only want certain fields returned and can include all of these fields in the index,
MongoDB can do a
covered index
query, where it never has to follow the pointers to
documents and just returns the index's data to the client. So, for example, suppose we
have an index on some set of fields:
> db.foo.ensureIndex({"x" : 1, "y" : 1, "z" : 1})
Then if we query on the indexed fields and only request the indexed fields returned,
there's no reason for MongoDB to load the full document:
> db.foo.find({"x" :
criteria
, "y" :
criteria
},
... {"x" : 1, "y" : 1, "z" : 1, "_id" : 0})
Now this query will only touch the data in the index, it never has to touch the collection
proper.
Notice that we include a clause
"_id" : 0
in the fields-to-return argument. The
_id
is
always returned, by default, but it's not part of our index so MongoDB would have to
