Database Reference
In-Depth Information
2.2.2
Indexing and explain()
If you've spent time working with relational databases, you're probably familiar with
SQL
's
EXPLAIN
.
EXPLAIN
describes query paths and allows developers to diagnose slow
operations by determining which indexes a query has used. MongoDB has its own ver-
sion of
EXPLAIN
that provides the same service. To get an idea of how it works, let's
apply it to one of the queries you just issued. Try running the following on your system:
> db.numbers.find( {num: {"$gt": 199995 }} ).explain()
The result should look something like what you see in the following listing.
Listing 2.1
Typical
explain()
output for an unindexed query
{
"cursor" : "BasicCursor",
"nscanned" : 200000,
"nscannedObjects" : 200000,
"n" : 4,
"millis" : 171,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" : { }
}
Examining the
explain()
output, you may be surprised to see that the query engine
has to scan the entire collection, all 200,000 documents (
nscanned
), to return just
four results (
n
). The
BasicCursor
cursor type verifies that this query hasn't used an
index to return the result set. Such a large difference between the number of docu-
ments scanned and the number returned marks this as an inefficient query. In a real-
world situation, where the collection and the documents themselves would likely be
larger, the time needed to process the query would be substantially greater than the
171 milliseconds noted here.
What this collection needs is an index. You can create an index for the
num
key
using the
ensureIndex()
method. Try entering the following index creation code
yourself:
> db.numbers.ensureIndex({num: 1})
As with other MongoDB operations, such as queries and updates, you pass a docu-
ment to the
ensureIndex()
method to define the index's keys. In this case, the
{num:
1}
document indicates that an ascending index should be built on the
num
key for all
documents in the
numbers
collection.
You can verify that the index has been created by calling the
getIndexes()
method:
> db.numbers.getIndexes()
[
{
