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

Key Takeaways

• Indexes use a B-Tree structure to speed up database queries.
• Following the ESR rule helps optimize compound indexes.
• Highly selective queries improve performance significantly.
• Proper index design prevents costly disk operations.

Prefix: What is an index?

An index makes querying your database faster by storing a portion of your collection's data in a B-Tree data structure.

The B-Tree ensures efficient access paths, keeping query times consistent regardless of your data's size.

What's a B-Tree?

• A B-Tree's balanced structure means all leaf nodes are at the same depth, keeping performance predictable, with no document more than 3-4 I/O operations away.
• Leaf nodes link to the next and previous ones, making range queries efficient and fast.
• The downside is that as data grows, maintaining this structure through splits can become computationally expensive.

The ESR Rule

The ESR rule — Equality, Sorting, Range — guides how to order fields in a compound index to optimize query performance.

Equality

Equality matches should typically be first in your index, especially for fields that are highly selective, filtering out the majority of records rapidly. This reduction in scanned records allows the database to handle more resource-intensive operations like sorting more effectively.

Sorting

Indexes inherently sort records. For instance:

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

The above command sorts names in descending order, leveraging indexes for sorting whenever the query predicate matches the index. Without an appropriate index, queries result in blocking sorts, which are much less efficient as they require sorting all documents actively before returning any results.

Range

Range queries leverage the B-Tree's order, but should be used wisely within the ESR rule for maximum efficiency.

Selectivity

High selectivity queries, which filter out a large portion of documents using an equality condition, see significantly better performance. Understanding your data's cardinality — the uniqueness of values in an indexed field — is crucial in designing efficient indexes.

Fitting the Index Inside RAM

Ensuring that your indexes fit in RAM is crucial for performance. The B-Tree structure means that more nodes in memory allow for faster access, avoiding the need for costly disk I/O operations. As data scales, managing the index size to prevent excessive splits is vital to maintain performance.

FAQ

Why is the B-Tree structure important for indexes?

The B-Tree structure balances each node's depth, allowing for predictable performance across queries by minimizing the I/O operations needed to access data.

How does the ESR rule optimize index performance?

By ordering index fields as Equality, Sorting, Range, you ensure the most efficient use of the index by prioritizing highly selective equality conditions and leveraging natural sorting.

What happens if my index doesn't fit in RAM?

If your index doesn't fit in RAM, MongoDB must load parts of it from disk, significantly slowing down query performance due to increased I/O operations.

How do I know if a query is highly selective?

A query is highly selective if it filters out the vast majority of the dataset, usually 90% or more, based on equality conditions. This dramatically reduces the workload for subsequent operations.