Indexes

Estimated time to read: 3 minutes

Indexes allow making queries more efficient.

Benefits of an Index, an analogy

An Index in a database functions similar to an index in a book. For example, Find all mentions of Toni Morrison within a Book.

Not using an Index	Using an Index
Look through every page in the book	Look through an alphabetically organized index
	Go to the M entry
	Locate Morrison
	Mark all relevant pages
	View all relevant pages

Within MongoDB

An index in a collection is a special data structure, it stores a small portion of the collections data set in an easy to traverse form. An index is essentially a data structure that optimisms queries.

When to Index

An index should be built to support often run queries, for example:

Given the following commands: db.trips.find( {"birth_year": 1989} ) db.trips.find( {"start station id": 476} ).sort("birth year": 1)

Note that both of the commands above reference the birth year field. This could imply that an index storing the birth year values may be suitable for optimizing queries.

Creating an Index

Based on the example above, to create an index for the birth year values, the following commands can be used:

db.trips.createIndex( {"birth year" : 1} )

How is Indexing better?

Following on with the example above, having created the index.

db.trips.find( {"birth_year": 1989} ) now refers to the index and jumps directly to the documents that have value, 1989, in the birth year field.

db.trips.find( {"start station id": 476} ).sort("birth year": 1) this still looks through all the documents in the collection to find the station ID. Though it will use the birth year index when it is sorted.

Avoid Sorting

Sorting is both compute and memory intensive. Using an index reduces the intensiveness of the find operations.

Types of Index

Single Field Index

A Single Field Index creates an index only for a specific field. However, a Single Field Index is not suitable for compounded queries.

A Single Field Index can be created using:

db.trips.createIndex( {"birth year" : 1} )

Compound Index

A Compound Index creates an index on multiple fields. These are beneficial when querying by multiple fields.

db.trips.createIndex( {"start station id"" 1, "birth year" : 1} )

This index will first order documents by the start station ID value then by the birth year value.

It coexists with the previous index in the same collection, however, this compound index is better suited for the second query. It helps to immediately locate all start stations with ID 476, thanks to the Compound Index, the birth year documents are already sorted.

Single Field Index vs Compound Index

Usage

db.trips.find({ "start station id": 476 }).sort( { "birth year": 1 } )

Find all documents that have the value 476 in the start station id field.

Once all documents have been returned with the required value, the sort() method is then used to sort by birth year, though this has been done implicitly as the Index has stored these documents in descending order.