MongoDB Indexing, Part 1
Key Takeaways
- Indexing in MongoDB can greatly enhance performance and throughput by reducing the number of full documents that need to be read, thus improving application performance.
- MongoDB supports several types of indexes, including Default _id Index, Secondary Index, Compound Index, Multikey Index, and Multikey Compound Index. Each type serves a specific purpose and is used for different types of queries.
- More than one index can be defined on a collection, but a query can only use one index during its execution. The best index is chosen at runtime by MongoDB’s query-optimizer.
- While indexing can dramatically improve read operations, it also incurs its own costs. Indexing operations occupy space and cause extra overhead on each insert, update, and delete operation on the collection. Therefore, indexing benefits read-heavy collections more than write-heavy collections.
- Default _id Index
- Secondary Index
- Compound Index
- Multikey Index
- Multikey Compound Index
posts
populated with 500 documents having the following structure:
{
"_id": ObjectId("5146bb52d852470060001f4"),
"comments": {
"0": "This is the first comment",
"1": "This is the second comment"
},
"post_likes": 40,
"post_tags": {
"0": "MongoDB",
"1": "Tutorial",
"2": "Indexing"
},
"post_text": "Hello Readers!! This is my post text",
"post_type": "private",
"user_name": "Mark Anthony"
}
Now, let’s explore various types of indexing in detail.
Default _id Index
By default, MongoDB creates a default index on the_id
field for each collection. Each document has a unique _id
field as a primary key, a 12-byte ObjectID. When there are no other any indexes available, this is used by default for all kinds of queries.
To view the indexes for a collection, open the MongoDB shell and do the following:
getIndexes()
method returns all of the indexes for our collection. As you can see, we have the default index with name _id_. The key
field indicates that the index is on the _id
field, and the value of 1 indicates an ascending order. We’ll learn about ordering in next section.
Secondary Index
For cases where we want to use indexing on fields other than_id
field, we have to define custom indexes. Suppose we want to search for posts based on the user_name
field. In this case, we’ll define a custom index on the user_name
field of the collection. Such custom indexes, other than the default index, are called secondary indexes.
To demonstrate the effect of indexing on database, let’s briefly analyze query performance without indexing first. For this, we’ll execute a query to find all posts having a user_name
with “Jim Alexandar”.
<?php
// query to find posts with user_name "Jim Alexandar"
$cursor = $collection->find(
array("user_name" => "Jim Alexandar")
);
// use explain() to get explanation of query indexes
var_dump($cursor->explain());
An important method often used with indexing is explain()
which returns information relevant to indexing. The output of the above explain()
is as shown below:
cursor
– indicates the index used in the query. BasicCursor indicates that the default _id index was used and MongoDB had to search the entire collection. Going ahead, we’ll see that when we apply indexing, BtreeCursor will be used instead of BasicCursor.n
– indicates the number of documents the query returned (one document in this case).nscannedObjects
– indicates the number of documents searched by the query (in this case, all 500 documents of the collection were searched). This can be an operation with large overhead if the number of documents in collection is very large.nscanned
– indicates the number of documents scanned during the database operation.
n
should be equal to or near to nscanned
, which means a minimum number of documents were searched.
Now, let’s execute the same query but using a secondary index. To create the index, execute the following in the MongoDB shell:
user_name field
in the posts
collection using the ensureIndex()
method. I’m sure you’ve niced the value of the order argument to the method which indicates either an ascending (1) or descending (-1) order for the search. To better understand this, note that each document has a timestamp field. If we want the most recent posts first, we would use descending order. For the oldest posts first, we would choose ascending order.
After creating the index, the same find()
and explain()
methods are used to execute and analyze the query as before. The output of is:
Compound Index
There will be cases when a query uses more than one field. In such cases, we can use compound indexes. Consider the following query which uses both thepost_type
and post_likes
fields:
<?php
// query to find posts with type public and 100 likes
$cursor = $collection->find(
array(
"post_type" => "public",
"post_likes" => 100
),
array()
);
Analyzing this query with explain()
, gives the following result, which shows that the query uses BasicCursor
and all 500 documents are scanned to retrieve one document.
post_type
and post_likes
as follows:
{field1,field2,field3}
. This index can be used to query on:
field1
field1, field2
field1, field2, field3
{field1,field2,field3}
, we don’t need to define separate {field1}
and {field1,field2}
indexes. However, if we need this compound index while querying field2
and field2,field3
, we can use hint()
if the optimizer doesn’t select the desired index.
The hint()
method can be used to force MongoDB to use an index we specify and override the default selection and query optimization process. You can specify the field names used in the index as a argument as shown below:
<?php
// query to find posts with type public and 100 likes
// use hint() to force MongoDB to use the index we created
$cursor = $collection
->find(
array(
"post_type" => "public",
"post_likes" => 100
)
)
->hint(
array(
"post_type" => 1,
"post_likes" => 1
)
);
This ensures the query uses the compound index defined on the post_type
and post_likes
fields.
Multikey Index
When indexing is done on an array field, it is called a multikey index. Consider ourpost
document again; we can apply a multikey index on post_tags
. The multikey index would index each element of the array, so in this case separate indexes would be created for the post_tags
values: MongoDB, Tutorial, Indexing, and so on.
Multikey Compound Index
We can create a multikey compound index, but with the limitation that at most one field in the index can be an array. So, if we havefield1
as a string, and [field2, field3]
as an array, we can’t define the index {field2,field3}
since both fields are arrays.
In the example below, we create an index on the post_tags
and user_name
fields:
Indexing Limitations and Considerations
It is important to know that indexing can’t be used in queries which use regular expressions, negation operators (i.e.$ne
, $not
, etc.), arithmetic operators (i.e. $mod
, etc.), JavaScript expressions in the $where
clause, and in some other cases.
Indexing operations also come with their own cost. Each index occupies space as well as causes extra overhead on each insert, update, and delete operation on the collection. You need to consider the read:write ratio for each collection; indexing is beneficial to read-heavy collections, but may not be for write-heavy collections.
MongoDB keeps indexes in RAM. Make sure that the total index size does not exceed the RAM limit. If it does, some indexes will be removed from RAM and hence queries will slow down. Also, a collection can have a maximum of 64 indexes.
Summary
That’s all for this part. To summarize, indexes are highly beneficial for an application if a proper indexing approach is chosen. In the next part, we’ll look at using indexes on embedded documents, sub-documents, and ordering. Stay tuned! Image via FotoliaFrequently Asked Questions about MongoDB Indexing
What is the importance of MongoDB indexing in database management?
MongoDB indexing is a critical aspect of database management. It significantly improves the performance of database operations by providing a more efficient path to the data. 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. With indexes, MongoDB can limit its search to the relevant parts of the data, thereby reducing the amount of data it needs to scan. This results in faster query response times and lower CPU usage, which is particularly beneficial in large databases.
How does MongoDB indexing work?
MongoDB indexing works by creating a special data structure that holds a small portion of the collection’s data. This data structure includes the value of a specific field or set of fields, ordered by the value of the field as specified in the index. When a query is executed, MongoDB uses these indexes to limit the number of documents it must inspect. Indexes are particularly beneficial when the total size of the documents exceeds the available RAM.
What are the different types of indexes in MongoDB?
MongoDB supports several types of indexes that you can use to improve the performance of your queries. These include Single Field, Compound, Multikey, Text, 2d, and 2dsphere indexes. Each type of index serves a specific purpose and is used for different types of queries. For example, Single Field and Compound indexes are used for queries on single or multiple fields, respectively. Multikey indexes are used for arrays, and Text indexes are used for string content.
How do I create an index in MongoDB?
You can create an index in MongoDB using the createIndex() method. This method creates an index on a specified field if the index does not already exist. The method takes two parameters: the field or fields to index and an options document that allows you to specify additional options.
Can I create multiple indexes in MongoDB?
Yes, you can create multiple indexes in MongoDB. However, it’s important to note that while indexes improve query performance, they also consume system resources, particularly disk space and memory. Therefore, it’s crucial to create indexes judiciously and only on those fields that will be frequently queried.
How do I choose which fields to index in MongoDB?
The choice of which fields to index in MongoDB largely depends on your application’s query patterns. Fields that are frequently queried or used in sort operations are good candidates for indexing. Additionally, fields with a high degree of uniqueness are also good candidates for indexing as they can significantly reduce the number of documents MongoDB needs to scan when executing a query.
How can I check if an index exists in MongoDB?
You can check if an index exists in MongoDB using the getIndexes() method. This method returns a list of all indexes on a collection, including the _id index which is created by default.
Can I delete an index in MongoDB?
Yes, you can delete an index in MongoDB using the dropIndex() method. This method removes the specified index from a collection.
What is index intersection in MongoDB?
Index intersection is a feature in MongoDB that allows the database to use more than one index to fulfill a query. This can be particularly useful when no single index can satisfy a query, but the intersection of two or more indexes can.
What is the impact of indexing on write operations in MongoDB?
While indexing significantly improves the performance of read operations, it can have an impact on write operations. This is because each time a document is inserted or updated, all indexes on the collection must also be updated. Therefore, the more indexes a collection has, the slower the write operations will be. It’s important to find a balance between read performance and write performance when creating indexes.
Ashish is a Computer Science Engineer and works for one of the Big Four firms as a Technology Analyst. Prior to this, he co-founded an educational start-up. In his free time, Ashish read and write blogs, watches animation cartoons, movies or discovery channel. You can find his recent blogs on http://ashishtrivediblog.wordpress.com/
Published in
·Miscellaneous·Patterns & Practices·Performance & Scaling·PHP·Programming·Web·February 17, 2014