Databases Reference
In-Depth Information
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 topic” to find the desired dates. This process
is called a
table scan
, which is basically what you'd do if you were looking for infor-
mation in a topic 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 }
