It can be tricky to figure out what the optimal index for your queries is, but it is well worth the effort. Queries that otherwise take minutes can be instantaneous with the proper indexes.

Suppose that you are querying for a single key, such as the following:

> db.people.find({"username" : "mark"})

When only a single key is used in the query, that key can be indexed to improve the query’s speed. In this case, you would create an index on "username". To create the index, use the ensureIndex method:

> db.people.ensureIndex({"username" : 1})

An index needs to be created only once for a collection. If you try to create the same index again, nothing will happen.

An index on a key will make queries on that key fast. However, it may not make other queries fast, even if they contain the indexed key. For example, this wouldn’t be very performant with the previous index:

> db.people.find({"date" : date1}).sort({"date" : 1, "username" : 1})

The server has to “look through the whole book” to find the desired dates. This process is called a table scan, which is basically what you’d do if you were looking for information in a book without an index: you start at page 1 and read through the whole thing. In general, you want to avoid making the server do table scans, because it is very slow for large collections.

As a rule of thumb, you should create an index that contains all of the keys in your query. For instance, to optimize the previous query, you should have an index on date and username:

> db.ensureIndex({"date" : 1, "username" : 1})

The document you pass to ensureIndex is of the same form as the document passed to the sort function: a set of keys with value 1 or -1, depending on the direction you want the index to go. If you have only a single key in the index, direction is irrelevant. A single key index is analogous to a book’s index that is organized in alphabetical order: whether it goes from A–Z or Z–A, it’s going to be fairly obvious where to find entries starting with M.

If you have more than one key, you need to start thinking about index direction. For example, suppose we have a collection of users:

{ "_id" : ..., "username" : "smith", "age" : 48, "user_id" : 0 }
{ "_id" : ..., "username" : "smith", "age" : 30, "user_id" : 1 }
{ "_id" : ..., "username" : "john", "age" : 36, "user_id" : 2 }
{ "_id" : ..., "username" : "john", "age" : 18, "user_id" : 3 }
{ "_id" : ..., "username" : "joe", "age" : 36, "user_id" : 4 }
{ "_id" : ..., "username" : "john", "age" : 7, "user_id" : 5 }
{ "_id" : ..., "username" : "simon", "age" : 3, "user_id" : 6 }
{ "_id" : ..., "username" : "joe", "age" : 27, "user_id" : 7 }
{ "_id" : ..., "username" : "jacob", "age" : 17, "user_id" : 8 }
{ "_id" : ..., "username" : "sally", "age" : 52, "user_id" : 9 }
{ "_id" : ..., "username" : "simon", "age" : 59, "user_id" : 10 }

Let’s say we index them with {"username" : 1, "age" : -1}. MongoDB will organize the users as follows:

{ "_id" : ..., "username" : "jacob", "age" : 17, "user_id" : 8 }
{ "_id" : ..., "username" : "joe", "age" : 36, "user_id" : 4 }
{ "_id" : ..., "username" : "joe", "age" : 27, "user_id" : 7 }
{ "_id" : ..., "username" : "john", "age" : 36, "user_id" : 2 }
{ "_id" : ..., "username" : "john", "age" : 18, "user_id" : 3 }
{ "_id" : ..., "username" : "john", "age" : 7, "user_id" : 5 }
{ "_id" : ..., "username" : "sally", "age" : 52, "user_id" : 9 }
{ "_id" : ..., "username" : "simon", "age" : 59, "user_id" : 10 }
{ "_id" : ..., "username" : "simon", "age" : 3, "user_id" : 6 }
{ "_id" : ..., "username" : "smith", "age" : 48, "user_id" : 0 }
{ "_id" : ..., "username" : "smith", "age" : 30, "user_id" : 1 }

The usernames are in strictly increasing alphabetical order, and within each name group the ages are in decreasing order. This optimizes sorting by {"username" : 1, "age" : -1} but is less efficient at sorting by {"username" : 1, "age" : 1}. If we wanted to optimize {"username" : 1, "age" : 1}, we would create an index on {"username" : 1, "age" : 1} to organize ages in ascending order.

The index on username and age also makes queries on username fast. In general, if an index has N keys, it will make queries on any prefix of those keys fast. For instance, if we have an index that looks like {"a" : 1, "b" : 1, "c" : 1, ..., "z" : 1}, we effectively have an index on {"a" : 1}, {"a" : 1, "b" : 1}, {"a" : 1, "b" : 1, "c" : 1}, and so on. Queries that would use the index {"b" : 1}, {"a" : 1, "c" :1}, and so on will not be optimized: only queries that can use a prefix of the index can take advantage of it.

The MongoDB query optimizer will reorder query terms to take advantage of indexes: if you query for {"x" : "foo", "y" : "bar"} and you have an index on {"y" : 1, "x" : 1}, MongoDB will figure it out.

The disadvantage to creating an index is that it puts a little bit of overhead on every insert, update, and remove. This is because the database not only needs to do the operation but also needs to make a note of it in any indexes on the collection. Thus, the absolute minimum number of indexes should be created. There is a built-in maximum of 64 indexes per collection, which is more than almost any application should need.

Sometimes the most efficient solution is actually not to use an index. In general, if a query is returning a half or more of the collection, it will be more efficient for the database to just do a table scan instead of having to look up the index and then the value for almost every single document. Thus, for queries such as checking whether a key exists or determining whether a boolean value is true or false, it may actually be better to not use an index at all.

> db.status.ensureIndex({user : 1, date : -1})

User 123 on March 13, 2010
User 123 on March 12, 2010
User 123 on March 11, 2010
User 123 on March 5, 2010
User 123 on March 4, 2010
User 124 on March 12, 2010
User 124 on March 11, 2010
...

> db.blog.ensureIndex({"comments.date" : 1})

> db.foo.ensureIndex({"a" : 1, "b" : 1, "c" : 1, ..., "z" : 1}, {"name" : "alphabet"})

Unique indexes guarantee that, for a given key, every document in the collection will have a unique value. For example, if you want to make sure no two documents can have the same value in the "username" key, you can create a unique index:

> db.people.ensureIndex({"username" : 1}, {"unique" : true})

Keep in mind that insert, by default, does not check whether the document was actually inserted. Therefore, you may want to use safe inserts if you are inserting documents that might have a duplicate value for a unique key. This way, you will get a duplicate key error when you attempt to insert such a document.

A unique index that you are probably already familiar with is the index on "_id", which is created whenever you create a normal collection. This is a normal unique index, aside from the fact that it cannot be deleted.

> db.people.ensureIndex({"username" : 1}, {"unique" : true, "dropDups" : true})

{files_id : ObjectId("4b23c3ca7525f35f94b60a2d"), n : 1}
{files_id : ObjectId("4b23c3ca7525f35f94b60a2d"), n : 2}
{files_id : ObjectId("4b23c3ca7525f35f94b60a2d"), n : 3}
{files_id : ObjectId("4b23c3ca7525f35f94b60a2d"), n : 4}

explain is an incredibly handy tool that will give you lots of information about your queries. You can run it on any query by tacking it on to a cursor. explain returns a document, not the cursor itself, unlike most cursor methods:

> db.foo.find().explain()

explain will return information about the indexes used for the query (if any) and stats about timing and the number of documents scanned.

Let’s take an example. The index {"username" : 1} works well to speed up a simple key/value pair lookup, but many queries are more complicated. For example, suppose you are querying and sorting the following:

> db.people.find({"age" : 18}).sort({"username" : 1})

You may be unsure if the database is using the index you created or how effective it is. If you run explain on the query, it will return the index currently being used for the query, how long it took, and how many documents the database needed to scan to find the results.

For a very simple query ({}) on a database with no indexes (other than the index on "_id") and 64 documents, the output for explain looks like this:

> db.people.find().explain()
{
    "cursor" : "BasicCursor",
    "indexBounds" : [ ],
    "nscanned" : 64,
    "nscannedObjects" : 64,
    "n" : 64,
    "millis" : 0,
    "allPlans" : [
        {
            "cursor" : "BasicCursor",
            "indexBounds" : [ ]
        }
    ]
}

The important parts of this result are as follows:

Now, let’s suppose we have an index on the "age" key and we’re querying for users in their 20s. If we run explain on this query, we’ll see the following:

> db.c.find({age : {$gt : 20, $lt : 30}}).explain()
{
    "cursor" : "BtreeCursor age_1",
    "indexBounds" : [
        [
            {
                "age" : 20
            },
            {
                "age" : 30
            }
        ]
    ],
    "nscanned" : 14,
    "nscannedObjects" : 12,
    "n" : 12,
    "millis" : 1,
    "allPlans" : [
        {
            "cursor" : "BtreeCursor age_1",
            "indexBounds" : [
                [
                    {
                        "age" : 20
                    },
                    {
                        "age" : 30
                    }
                ]
            ]
        }
    ]
}

There are some differences here, because this query uses an index. Thus, some of explain’s outputted keys have changed:

Let’s take a slightly more complicated example: suppose you have an index on {"username" : 1, "age" : 1} and an index on {"age" : 1, "username" : 1}. What happens if you query for username and age? Well, it depends on the query:

> db.c.find({age : {$gt : 10}, username : "sally"}).explain()
{
        "cursor" : "BtreeCursor username_1_age_1",
        "indexBounds" : [
                [
                        {
                                "username" : "sally",
                                "age" : 10
                        },
                        {
                                "username" : "sally",
                                "age" : 1.7976931348623157e+308
                        }
                ]
        ],
        "nscanned" : 13,
        "nscannedObjects" : 13,
        "n" : 13,
        "millis" : 5,
        "allPlans" : [
                {
                        "cursor" : "BtreeCursor username_1_age_1",
                        "indexBounds" : [
                                [
                                        {
                                                "username" : "sally",
                                                "age" : 10
                                        },
                                        {
                                                "username" : "sally",
                                                "age" : 1.7976931348623157e+308
                                        }
                                ]
                        ]
                }
        ],
        "oldPlan" : {
                "cursor" : "BtreeCursor username_1_age_1",
                "indexBounds" : [
                        [
                                {
                                        "username" : "sally",
                                        "age" : 10
                                },
                                {
                                        "username" : "sally",
                                        "age" : 1.7976931348623157e+308
                                }
                        ]
                ]
        }
}

We are querying for an exact match on "username" and a range of values for "age", so the database chooses to use the {"username" : 1, "age" : 1} index, reversing the terms of the query. If, on the other hand, we query for an exact age and a range of names, MongoDB will use the other index:

> db.c.find({"age" : 14, "username" : /.*/}).explain()
{
        "cursor" : "BtreeCursor age_1_username_1 multi",
        "indexBounds" : [
                [
                        {
                                "age" : 14,
                                "username" : ""
                        },
                        {
                                "age" : 14,
                                "username" : {
                                        
                                }
                        }
                ],
                [
                        {
                                "age" : 14,
                                "username" : /.*/
                        },
                        {
                                "age" : 14,
                                "username" : /.*/
                        }
                ]
        ],
        "nscanned" : 2,
        "nscannedObjects" : 2,
        "n" : 2,
        "millis" : 2,
        "allPlans" : [
                {
                        "cursor" : "BtreeCursor age_1_username_1 multi",
                        "indexBounds" : [
                                [
                                        {
                                                "age" : 14,
                                                "username" : ""
                                        },
                                        {
                                                "age" : 14,
                                                "username" : {
                                                        
                                                }
                                        }
                                ],
                                [
                                        {
                                                "age" : 14,
                                                "username" : /.*/
                                        },
                                        {
                                                "age" : 14,
                                                "username" : /.*/
                                        }
                                ]
                        ]
                }
        ]
}

If you find that Mongo is using different indexes than you want it to for a query, you can force it to use a certain index by using hint. For instance, if you want to make sure MongoDB uses the {"username" : 1, "age" : 1} index on the previous query, you could say the following:

> db.c.find({"age" : 14, "username" : /.*/}).hint({"username" : 1, "age" : 1})

Hinting is usually unnecessary. MongoDB has a query optimizer and is very clever about choosing which index to use. When you first do a query, the query optimizer tries out a number of query plans concurrently. The first one to finish will be used, and the rest of the query executions are terminated. That query plan will be remembered for future queries on the same keys. The query optimizer periodically retries other plans, in case you’ve added new data and the previously chosen plan is no longer best. The only part you should need to worry about is giving the query optimizer useful indexes to choose from.

Metainformation about indexes is stored in the system.indexes collection of each database. This is a reserved collection, so you cannot insert documents into it or remove documents from it. You can manipulate its documents only through ensureIndex and the dropIndexes database command.

The system.indexes collection has detailed information about each index, but the system.namespaces collection also lists their names. If you look at this collection, you can see that there are at least two documents for each collection: one for the collection itself and one for each index it contains. For a collection with just the standard "_id" index, its system.namespaces entries would look like this:

{ "name" : "test.foo" }
{ "name" : "test.foo.$_id_"}

If we add a compound index on the name and age keys, a new document is added to system.namespaces with the following form:

{ "name" : "test.foo.$name_1_age_1" }

In Chapter 2, we mentioned that collection names are limited to 121 bytes in length. This somewhat strange limit is because the "_id" index’s namespace needs 6 extra bytes (".$_id_"), bringing the namespace length for that index up to a more sensical 127 bytes.

It is important to remember that the size of the collection name plus the index name cannot exceed 127 bytes. If you approach the maximum namespace size or have large index names, you may have to use custom index names to avoid creating namespaces that are too long. It’s generally easier just to keep database, collection, and key names to reasonable lengths.

> db.people.ensureIndex({"username" : 1}, {"background" : true})

> db.runCommand({"dropIndexes" : "foo", "index" : "alphabet"})

> db.runCommand({"dropIndexes" : "foo", "index" : "*"})

There is another type of query that is becoming increasingly common (especially with the emergence of mobile devices): finding the nearest N things to a current location. MongoDB provides a special type of index for coordinate plane queries, called a geospatial index.

Suppose we wanted to find the nearest coffee shop to where we are now, given a latitude and longitude. We need to create a special index to do this type of query efficiently, because it needs to search in two dimensions. A geospatial index can be created using the ensureIndex function, but by passing "2d" as a value instead of 1 or -1:

> db.map.ensureIndex({"gps" : "2d"})

The "gps" key must be some type of pair value, that is, a two-element array or embedded document with two keys. These would all be valid:

{ "gps" : [ 0, 100 ] }
{ "gps" : { "x" : -30, "y" : 30 } }
{ "gps" : { "latitude" : -180, "longitude" : 180 } }

The keys can be anything; for example, {"gps" : {"foo" : 0, "bar" : 1}} is valid.

By default, geospatial indexing assumes that your values are going to range from -180 to 180 (which is convenient for latitude and longitude). If you are using it for other values, you can specify what the minimum and maximum values will be as options to ensureIndex:

> db.star.trek.ensureIndex({"light-years" : "2d"}, {"min" : -1000, "max" : 1000})

This will create a spatial index calibrated for a 2,000 × 2,000 light-year square.

Geospatial queries can be performed in two ways: as a normal query (using find) or as a database command. The find approach is similar to performing a nongeospatial query, except the conditional "$near" is used. It takes an array of the two target values:

> db.map.find({"gps" : {"$near" : [40, -73]}})

This finds all of the documents in the map collection, in order by distance from the point (40, -73). A default limit of 100 documents is applied if no limit is specified. If you don’t need that many results, you should set a limit to conserve server resources. For example, the following code returns the 10 nearest documents to (40, -73):

> db.map.find({"gps" : {"$near" : [40, -73]}}).limit(10)

This same type of query can be performed using the geoNear command:

> db.runCommand({geoNear : "map", near : [40, -73], num : 10});

geoNear also returns the distance of each result document from the query point. The distance is in whatever units you inserted the data in; if you inserted degrees of longitude and latitude, the distance is in degrees. find with "$near" doesn’t give you the distance for each point but must be used if you have more than 4MB of results.

MongoDB also allows you to find all of the documents within a shape, as well as near a point. To find points in a shape, we use the "$within" conditional instead of the "$near" conditional. "$within" can take an increasing number of shapes; check the online documentation for geospatial indexing to see the most up-to-date list of what’s supported (http://www.mongodb.org/display/DOCS/Geospatial+Indexing). As of this writing, there are two options: you can query for all points within a rectangle or a circle. To use a rectangle, use the "$box" option:

> db.map.find({"gps" : {"$within" : {"$box" : [[10, 20], [15, 30]]}}})

"$box" takes a two-element array: the first element specifies the coordinates of the lower-left corner, the second element the upper right.

Also, you can find all points within a circle with "$center", which takes an array with the center point and then a radius:

> db.map.find({"gps" : {"$within" : {"$center" : [[12, 25], 5]}}})

> db.ensureIndex({"location" : "2d", "desc" : 1})

> db.map.find({"location" : {"$near" : [-70, 30]}, "desc" : "coffeeshop"}).limit(1)
{
    "_id" : ObjectId("4c0d1348928a815a720a0000"),
    "name" : "Mud",
    "location" : [x, y],
    "desc" : ["coffee", "coffeeshop", "muffins", "espresso"]
}