Index Performance | MongoDB Indexing Tutorial

Compound Index | MongoDB Indexing Tutorial

Index Performance | MongoDB Indexing Tutorial

Indexing Types | MongoDB Indexing Tutorial

Unique Index | MongoDB Indexing Tutorial

List Indexes | MongoDB Indexing Tutorial

Create Index | MongoDB Indexing Tutorial

Prefix: What is an index?

An index makes querying your database faster.

An index stores a portion of your collection's data in a B-Tree data structure.

What's a B-Tree?

  • each leaf node same depth makes performance predictable no document more than 3/4 i/os away
  • links to previous and next leaf block make range queries possible
  • con is index splits can get expensive

The ESR Rule

Equality. Sorting. Range. This is typically the order in which indexed fields should be added to a compound index.

Equality

There are exceptions to this rule but usually equality matches should come first. Equality matches which are highly selective should filter roughly 90% of the index.

This quickly diminishes the number of records that must undergo more expensive sort and range based operations. By eliminating entires your DB doesn't care about, it can more easily perform non-blocking sorts.

Sorting

Remember that indexes store records in a sorted order...

db.collection.createIndex({name:-1})

This stores names in descending order.

This means we can leverage indexes for sort operations.

This is only true if the sort uses same index as query predicate.

If an index can't be used, then blocking sorts start to happen. All input docs must be sorted before returning.

This is why indexes so important for sorting because they prevent blocking sorts.

Range

Selectivity

Queries that are highly selective will have far better performance. A highly selective query is one that eliminates 90% of the records in the index / collection with an equality match...

When creating indexes, it's important to understand cardinality. Cardinality refers to the uniqueness of values in the indexed field.

Fitting Index Inside RAM

  • explanation of B-Tree splitting gets expensive

Your thoughts?