Index Performance | 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.
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.
Remember that indexes store records in a sorted order...
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.
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