27MongoDBIndexing

Indexing

Outline

Overview of indexing
Single key index
Compound key index
Sparse index
Multikey index
Hashed index
Geospacial index
Index administration

The main objective of indexing is to speed up processing of queries and to speed up data manipulation operations that require database search before manipulations on data.

Indexing can also be used to enforce uniqueness constraints on the given key-values pairs in a collection of documents. Creating a unique index blocks insertion of the documents with the same given key-value pairs

Indexing of collections of JSON documents significantly reduce amount of time needed to access the documents. Indexing eliminates sequential read of an entire collection of JSON documents. With an index only some of the documents must be read.

The simplest version of an index is a "single-key" index. Such index is the most appropriate for the queries that use {"key":"value"} templates (patterns)

For the queries that have query templates (patterns) over multiple keys, like for example {$and:[{"key1":"value1"},{"key2":"value2"}]} a compound index is the best option.

A compound index has some advantages and disadvantages. The main disadvantage ia higher storage consumption, Additionally, a compound index requires more frequent modifications because probability of modification of a compound key is higher probabilities of modification of single key indexes. A modification of a value associated with one of the keys of a compound key always delays data manipulation operations.

The main advantage of a compound index key is such that it can be used for a wider range of queries than two single key indexes.

If we have a compound index on (key1, key2) then the second index on key1 is not really needed to process queries that use {"key1":"value"} templates (patterns), however it may still speed up an access a bit.

If we have a compound index on (key1, key2) then the second index on key2 speeds up access a lot however the queries that use {"key2":"value"} templates (patterns) still can be processed with the index.

It means that, like in relational database systems an order of keys in a compound index may have a significant impact on performance and consumption of persistent storage.

Fo example, a compound index on (last-name, date-of-birth) is perfect for the queries over multiple keys like {$and:[{"last-name":"Bond"},{"date-of-birth":"01-JAN-1960"}]}. If in such a case an index is implemented as B*-tree then one vertical traversal is required to filter the documents on the given condition.

The same index can be quite effectively used for a query over a single key {"last-name":"Bond"}. Then a vertical traversal of B*-Tree index is required and and later one horizontal traversal of B*-tree leaf level limited few data block.

The same index can be less effectively used (but still better than full scan of a collection of BSON documents) for a query over a single key {"date-of-birth":"01-JAN-1960"}. Then a horizontal traversal of a complete B*-tree leaf level is required.

Overview of indexing

Indexes significantly reduce amount of time needed to access the documents
Without indexes all the documents in a collection must be accessed
Single-key index is the most appropriate for {"key":"value"} query conditions
For query conditions over multiple keys, for example
{$and:[{"key1":"value1"},{"key2":"value2"}]}
compound index is the best option
If we have a compound index on (key1, key2) then the second index on key1 is not really needed, however it may still speed up an access a bit
If we have a compound index on (key1, key2) then the second index on key2 speeds up access a lot
An order of keys in a compound index is very important

Indexing

MongoDB automatically creates an index on the values associate in BSON documents with a key "_id". It is a single key and unique index. Such index is needed to enforce uniqueness of the values associated with "_id" in a collection of BSON documents. Without such index enforcement of uniqueness of values associated with "_id" key would required a full scan of entire collection of the documents. With the index only a single vertical traversal of B*-tree is needed. and no access to the BSON documents in a collection is required.

Equality search over the values of "_id" is the fasted possible search. It is a very good argument to associated with a key "_id" meaningful values, like fo example student number, course code etc instead of artificially generate values (when a key "_id" is omitted at data entry and the system creates the unique vales by itself).

A method "createIndex" can be sued to create an index over a collection of BSON documents. for example, the following command creates a single key unique index on the values associated with a key "code" in a collection "department".

db.department.createIndex( {"code": 1}, {"unique":true} )

The second parameter {"unique":true} enforces uniqueness of the index. It means that each document in a collection "department" must have a different value associated with a key "code". An attempt to insert two documents with the same value of key "code" fails when enforcing a key constraint.

Unique index should be created before inserting any data and it cannot be created on a collection where duplicate keys exist

Single key indexes

An index on "_id" is an automatically created single-key index
Equality search over the values of "_id" is the fasted possible search
The following command creates a single key unique index on a key code
The index is unique because it enforces uniqueness of the values associated with key code, i.e. each document in a collection has a different value associated with a key code
An attempt to insert two documents with the same value of key code fails enforcing a key constraint
Unique index should be created before inserting any data
A unique index cannot be created on a collection where duplicated keys already exist

Single key indexes

The following command creates a single key nonunique index on a key budget

Indexing

Outline

Overview of indexing
Single key index
Compound key index
Sparse index
Multikey index
Hashed index
Geospacial index
Index administration

The same method "createIndex" can be used to create a compound key index. For example,

db.department.createIndex( {"budget":1, "total_staff_number":1}, {"unique":false} )

The compound index created above is perfect for the queries like

db.department.find({"budget":2000, :"total_staff_number":5})

Each key in B*-tree implementation of the compound index created above is a concatenation of the values associated with a key "budget" and with a key "total_staff_number" later on. It means that an order of the arguments of "createIndex" method is very important if the index supposed to be used to speed up single key queries. In the present case as single key query with a condition like {"budget":5}, benefits more from the compound index than a query with a condition like {"total_staff_number":300}. If the second query is use more frequently then it is worth to re-build the index with the opposite order of the arguments.

Of course, it is possible to speed processing of the queries like

db.department.find({"budget":2000, :"total_staff_number":5})

with two single key nonunique indexes on the values associated with the keys "budget" and "total_staff_number".

Compound key index

The following commands create the single key nonunique indexes on the keys budget and total_staff_number


db.department.createIndex( {"budget":1}, {"unique":false} )


db.department.createIndex( {"total_staff_number":1}, {"unique":false} )

A query like


db.department.find({"budget":2000, :"total_staff_number":5})

uses only one of the indexes created above
A query optimizer picks the more efficient index (with higher selectivity)
To use both indexes we can traverse each index separately and calculate intersection of disk locations found

The following commands create the single key nonunique indexes on the keys "budget" and "total_staff_number".

db.department.createIndex( {"budget":1}, {"unique":false} )

db.department.createIndex( {"total_staff_number":1}, {"unique":false} )

Then, there three few possibilities to process the queries like

db.department.find({"budget":2000, :"total_staff_number":5})

The first one is two traverse vertically both indexes and then computer intersection of the sets of document identifiers obtained from the traversals and finally access a collection of documents.

The other two methods vertically traverse only one index, access a collection of documents and then with each document evaluate the second condition. An index to be vertically traversed must have a higher selectivity then the other to filter out as many documents as possible. MongoDB applies one of these methods dependig on selectivity of the indexes.

MongoDB uses only one of the indexes created above. A query optimizer finds selectivity and picks the more efficient index (with higher selectivity). Then the identifiers of the documents found from a vertical traversal are used to access a collection of the documents and to evaluated the second condition.

Compound key index

The following commands create a compound key nonunique index on the keys budget and total_staff_number
A compound index is a single index where each entry is composed of more than one key

A compound index is used by a query


db.department.find({"budget":2000, "total_staff_number":5})

Indexing

Outline

Overview of indexing
Single key index
Compound key index
Sparse index
Multikey index
Hashed index
Geospacial index
Index administration

In a dense index for every document there is an index key even the document lacks a key. For all documents that do not have a key an index contains a single null entry associated with with the identifiers of all such documents.

MongoDB indexes are dense by default. It means that a query like

db.department.find( {"budget":null} )

can be processed with an index.

In a sparse index, only documents that have a value for the indexed key are indexed. To create a sparse index a parameter "sparse":true must be provided for"createIndex" method, for example

db.department.createIndex( {"total_staff_number":1}, {"unique":false, "sparse":true} )

A sparse index cannot be sued when processing the queries retrieving the documents that have no given key(s).

A dense index is inconvenient when a unique index on a field that doesn’t appear in every document in a collection is needed. Then many values associated with null entry in an index violates uniqueness of the index.

A dense index is also inconvenient when a large number of documents in a collection have no indexed key. Then a lot of document identifiers is associted with a null entry in an index and it contributes to a heavily imbalanced index.

Sparse index

MongoDB indexes are dense by default
In a dense index for every document there is an index key even the document lacks a key
Then there exists a null entry in an index and it is possible to use an index for a query like
Dense index is inconvenient when:
- unique index on a field that doesn’t appear in every document in a collection is needed
- a large number of documents in a collection have no indexed key

In a sparse index, only documents that have a value for the indexed key are indexed

db.department.createIndex( {"total_staff_number":1},
	                   {"unique":false, "sparse":true} )

Indexing

Outline

Overview of indexing
Single key index
Compound key index
Sparse index
Multikey index
Hashed index
Geospacial index
Index administration

A multikey index is not used in relational database systems because of 1NF constraints imposed on the relational tables. The entries in 1NF tables cannot have many different values, only one or nothing (NULL).

In database systems that do not enforce 1NF constraint an atomic data can be a set or a sequence of values. In Mongo DB a key can be associated with an array of values (a sequence of values). Then in a multikey index it is possible that more than one value located at a leaf level of an index is associated with the same document identifier. In ordinary indexes an association between the index entries and document identifiers is one-to-many. One entry in an index can be associated with many document identifiers and each document identifier can be associated with only one entry in an index.

In multikey index an association between the index entries and document identifiers is many-to-many. One entry in an index can be associated with many document identifiers and each document identifier can be associated with many entries in an index. It is because a set or a sequence of values can be an atomic data item.

In MongoDB multiple entries in the index reference the same document because indexing key can be associated with the arrays of values.

For example it is possible to create the following multikey index

db.department.createIndex( {"course.code":1} )

where a document in a collection "department" contains information about the courses offered by each department. It is possible that two different departments offer the courses with the same code. Then, each value in this "courses.code" array will appear in the index

A query on any array values can use the index to locate the document.

Multikey index

In multikey index multiple entries in the index reference the same document
Multikey index is useful for indexing fields whose values are arrays
Each value in this courses.code array will appear in the index
A query on any array values can use the index to locate the document

Indexing

Outline

Overview of indexing
Single key index
Compound key index
Sparse index
Multikey index
Hashed index
Geospacial index
Index administration

Hashed index has a very different organization from B*-tree index. Hashed index is implemented as a function that partitions a collection of documents into so called "buckets". A value associated with an index key is extracted from a document and it is transformed into into a number of a bucket. Then a document is save in asuch bucket. Search is performed in almost the same way. A value associated with a key in a query templated is transformed into a number of a bucket. Then such bucket is searched for all document where a key is associated with a given value.

A hashed index on the values associated with a key "name" in BSON collection "department" can be create in the following way.

db.department.createIndex( {"name":"hashed"} )

Hashed indexes have the following restrictions:

- only equality queries can be processed with an index

- range queries cannot use hashed index

- multikey hashed indexes are not allowed

- floating-point values are cast to an integer before being hashed; 1.4 and 1.5 will have the same value in hashed index

Hashed indexes are used for sharding of the collections over the nodes of computer cluster.

Hashed index provides a better performance that B*-tree index.

Quality of hashed index depends on even partitioning of a collection over the buckets. It means that each bucket must contain more or less the same number of documents.

Therefore, a collection where a key is associated with the same value in a large number of buckets is not good candidate forhashed based index,

Hashed index

In hashed index the keys become the arguments of a hash function and a results of of hash function determine location of a document in a hash bucket
The hashed values will determine the ordering of the documents
Hashed indexes have the following restructions:
- equality queries can be processed with an index
- range queries cannot use hashed index
- multikey hashed indexes are not allowed
- floating-point values are cast to an integer before being hashed; 1.4 and 1.5 will have the same value in hashed index
Hashed indexes are used for sharding

Indexing

Outline

Overview of indexing
Single key index
Compound key index
Sparse index
Multikey index
Hashed index
Geospacial index
Index administration

Geospacial index allows to quickly find the documents that are close to a given location, based on latitude and longitude values stored in each document.

Geospacial index can be used to efficiently calculate geographic distances, including the curvature of the earth.

Geospacial index is usually implemented with a tree-like structure, for example as Quad-tree.

MongoDB supports different kinds of indexes, however, only the first two types of indexes listed below can be combined to a compound index.

1: Ascending B*-tree index

-1: Descending B*-tree index

"hashed": Hashtable index; very fast for lookup by exact value, especially in very large collections. But it is not usable for inexact queries ("$gt", "$regex" or similar)

"text": Text index designed for searching for words in strings with natural language

2d": Geospatial index on a flat plane

"2dsphere": Geospatial index on a sphere

Geospacial index

Geospacial index allows to find the documents that are close to a given location, based on latitude and longitude values stored in each document
Geospacial index can be used to efficiently calculate geographic distances, including the curvature of the earth
MongoDB supports different kinds of indexes, however, only the first two types of indexes listed below can be combined to a compound index
- 1: Ascending B*-tree index
- -1: Descending B*-tree index
- "hashed": Hashtable index; very fast for lookup by exact value, especially in very large collections. But it is not usable for inexact queries ("$gt", "$regex" or similar)
- "text": Text index designed for searching for words in strings with natural language
- "2d": Geospatial index on a flat plane
- "2dsphere": Geospatial index on a sphere

Indexing

Outline

Overview of indexing
Single key index
Compound key index
Sparse index
Multikey index
Hashed index
Geospacial index
Index administration

Index administration

Listing the indexes


db.department.getIndexes()


[
	{
		"v" : 2,
		"key" : {
			"_id" : 1
		},
		"name" : "_id_",
		"ns" : "test.department"
	}
]

Index administration

Creating an index


db.department.createIndex( {"name":"hashed"} )


{
	"createdCollectionAutomatically" : false,
	"numIndexesBefore" : 1,
	"numIndexesAfter" : 2,
	"ok" : 1
}

Index administration

Listing the indexes


db.department.getIndexes()


[
	{
		"v" : 2,
		"key" : {
			"_id" : 1
		},
		"name" : "_id_",
		"ns" : "test.department"
	},
	{
		"v" : 2,
		"key" : {
			"name" : "hashed"
		},
		"name" : "name_hashed",
		"ns" : "test.department"
	}
]

Index administration

Delete an index name_hashed


db.department.dropIndex("name_hashed")


db.department.getIndexes()


[
	{
		"v" : 2,
		"key" : {
			"_id" : 1
		},
		"name" : "_id_",
		"ns" : "test.department"
	}
]

An index can be created on an empty collection before data is loaded. It allows to build indexes incrementally as the data is inserted and the queries are processed and profiled. Profiling of the queries provides more information on what indexes should be created.

It is possible to create an index on an already existing collection of documents. If a collection is large then creation of an index on an already loaded collection may take a long time. It is possible to create an index in the background. An index can be create in background in the following way.

db.department.createIndex( {"total_staff_number":1},{"background":true} )

It is also possible to create an index offline by taking a replica node offline, building an index, and taking node online allowing the node to catch up with master replica node

When ready we can promote a node to primary and take another secondary node offline, etc.

Index administration

Creation of indexes before loading data allows indexes to be built incrementally as the data is inserted
Creation of an index on an already loaded collection may take a long time
It is possible to create an index in the background without closing a database system
It is possible to create an index in offline by taking a replica node offline, building an index, and taking node online allowing the node to catch up with master replica node
When ready we can promote a node to primary and take another secondary node offline, etc.

Index administration

It is possible to re-build indexes in order to defragment them after a lot of updates


db.department.reIndex()


{
	"nIndexesWas" : 1,
	"nIndexes" : 1,
	"indexes" : [
		{
			"v" : 2,
			"key" : {
				"_id" : 1
			},
			"name" : "_id_",
			"ns" : "test.department"
		}
	],
	"ok" : 1
}

References

Banker K., Bakkum P., Verch S., Garret D., Hawkins T., MongoDB in Action, 2nd ed., Manning Publishers, 2016
MongoDB Manual, Indexes https://url.au.m.mimecastprotect.com/s/vc8MC91WrrTXAOYDH3iXTqmCrv?domain=docs.mongodb.com
Chodorow K. MongoDB The Definitive Guide, O'Reilly, 2013