How to Master Database Indexing for Faster Queries

Imagine walking into a massive, multi-story library with millions of books. You need a specific novel by an author whose name you can’t quite recall, but you know it was published in 2015. The library has no catalog, no computers, and no organizational system. The books are just piled on shelves by the date they were donated.

You’d have to walk down every single aisle, scan every shelf, and inspect every book until you found the right one. This is a full collection scan, and it’s exactly how your MongoDB database feels when you run a query on a field that isn’t indexed.

A database index is that library’s card catalog. It’s a separate, highly organized data structure that maps values to their physical locations on disk. Instead of scanning a million documents to find one user with the email alex@example.com, the database can perform a lightning-fast lookup in the index, find the exact location, and retrieve the document in milliseconds.

This article is your practical guide to mastering database indexing, from the simple mental models you need to grasp the basics to the advanced strategies that will make your production queries scream. We’ll focus heavily on MongoDB but will bridge concepts to SQL and PostgreSQL where their behavior diverges, giving you a universal understanding of query optimization.

Table of Contents

• What Exactly is a Database Index?

• Why Indexes Improve Query Performance: The Science of Speed

• Your First Index: A Practical Example

• Compound Indexes: The Multi-Level Card Catalog

• The Golden Rule: Index Prefixes and the ESR Rule

• Index Cardinality: Targeting the Needle in the Haystack

• Covered Queries: The Ultimate Performance Hack

• TTL Indexes: Documents with an Expiration Date

• Sparse Indexes: Saving Space on Missing Fields

• Partial Indexes: An Unfair Advantage

• Text Search Indexes: Your Built-in Search Engine

• When NOT to Use an Index: The Cost of Speed

• Common Indexing Mistakes and Anti-Patterns

• Best Practices and a Real-World Optimization Walkthrough

• Frequently Asked Questions

• Conclusion and Next Steps

What Exactly is a Database Index?

At its core, an index is a lightweight, ordered copy of a subset of your data. Think of it not as a copy of the whole document, but as a signpost.

Let’s say you have a users collection:

{
  "_id": ObjectId("..."),
  "name": "Alex Johnson",
  "email": "alex@example.com",
  "age": 34,
  "city": "New York",
  "profession": "Software Engineer"
}

If you create an index on the email field, the database builds a structure that looks conceptually like this:

email (Index Key)Location Pointerabby@example.comRecord Location 405alex@example.comRecord Location 102zack@example.comRecord Location 890

This structure is kept meticulously sorted. Because it’s sorted, the database can use efficient search algorithms like binary search to find alex@example.com without scanning every entry.

Did You Know? This is conceptually identical in PostgreSQL, MySQL, and MongoDB. A B-Tree index in InnoDB (MySQL) serves the exact same purpose as a B-Tree index in MongoDB's WiredTiger storage engine.

Why Indexes Improve Query Performance: The Science of Speed

The performance difference isn't just significant; it's a fundamental shift in how the database operates.

• Without an Index (COLLSCAN): The database performs a linear scan. Time complexity is O(n)—the time it takes doubles as the dataset doubles.

• With an Index (IXSCAN): The database traverses a B-Tree. Time complexity is O(log n)—the time it takes grows very slowly even as the dataset explodes.

Imagine a collection of 1 billion documents. A linear scan might need to inspect, on average, 500 million documents. An index lookup can pinpoint a single value in roughly 30 steps. That’s not an optimization; that’s the difference between a function and a frozen application.

Note: Indexes also improve sort operations. Without an index, MongoDB loads all matching documents into memory to sort them, failing entirely if the sort exceeds the 32MB memory limit. An index returns the results in a pre-sorted stream, bypassing this bottleneck entirely.

Your First Index: A Practical Example

Let’s make this concrete. Assume a products collection with 500,000 documents.

// Our unoptimized query
db.products.find({ sku: "XJ-99-BLK" })

Step 1: Run an Explain Plan

db.products.find({ sku: "XJ-99-BLK" }).explain("executionStats")

The output will contain "stage": "COLLSCAN" and show "totalDocsExamined": 500000. It’s reading every document. This is a red flag in any database.

Step 2: Create the Index

// Create an ascending (1) index on the 'sku' field
db.products.createIndex({ sku: 1 })

Step 3: Re-run the Explain

db.products.find({ sku: "XJ-99-BLK" }).explain("executionStats")

The output now shows "stage": "IXSCAN", "totalDocsExamined": 1, and "totalKeysExamined": 1. The query is now orders of magnitude faster.

Compound Indexes: The Multi-Level Card Catalog

Single-field indexes are straightforward. But what if your primary query filters by city and sorts by age?

db.users.find({ city: "Portland" }).sort({ age: 1 })

Creating two separate indexes on city and age is a common mistake. In most cases, the database will use only one index to find the documents and then perform a slow, in-memory sort.

A compound index solves this brilliantly. It’s like a phone book organized by City, and within each City, by Last Name.

// Compound index on city, then age
db.users.createIndex({ city: 1, age: 1 })

Internally, the B-Tree now looks like this:

[Austin, 22] -> [Austin, 25] -> [Austin, 30] -> [Boise, 21] -> [Boise, 22] -> [Portland, 19] -> [Portland, 24] -> [Portland, 27]

The index is first sorted by city (ascending). For documents with the same city, it’s sorted by age (ascending). The database can now find the "Portland" block in the index, and stream the results back in perfect age order—no extra sorting required.

The PostgreSQL Parallel:

The Golden Rule: Index Prefixes and the ESR Rule

An index on { city: 1, age: 1, name: 1 } supports queries on the following index prefixes:

• { city: 1 }

• { city: 1, age: 1 }

• { city: 1, age: 1, name: 1 }

It does not efficiently support a query on { age: 1 } alone. Why? The B-Tree’s global sort is defined by city first. Without filtering by city, the age entries are scattered randomly across the entire index. The database would have to scan every leaf node.

Think of it like this: A phone book is perfectly indexed on {last_name, first_name}. Finding everyone with the first name "Alex" requires scanning the entire book. The first key in a compound index is your entry gate.

The ESR Rule for MongoDB

This is one of the most critical rules for crafting high-performance compound indexes. When designing for Equality, Sort, and Range, the order of your index keys matters enormously.

Equality First, Sort Second, Range Last.

db.orders.find({
  user_id: "u123", // E: Equality
  order_date: { $gte: ISODate("2023-01-01") } // R: Range
}).sort({
  total_amount: -1 // S: Sort
})

The optimal index for this query is:

// Yes! The correct order.
db.orders.createIndex({ user_id: 1, total_amount: -1, order_date: 1 })

Wait, why is total_amount (Sort) before order_date (Range)?

The ESR rule dictates the order of keys in the index, which is: { equality_field, sort_field, range_field }. Once a range query ($gte, $lte, $ne) is used on a key, the index stops being useful for sorting by subsequent keys. By placing the sort key before the range key, we ensure the results for user_id: "u123" come back perfectly sorted by total_amount, and then the range filter on order_date is applied.

Index Cardinality: Targeting the Needle in the Haystack

Index cardinality refers to the number of unique values in an index. A field with high cardinality has many unique values (e.g., email). A field with low cardinality has few unique values (e.g., is_active: true/false, gender).

Databases are cost-based optimizers. They estimate whether using an index is cheaper than just doing a full scan. For a very low-cardinality field, an index scan can actually be slower.

Imagine an index on gender with 1 million documents, 50% male, 50% female.

• Query: { gender: "male" }

• IXSCAN: The index finds 500,000 pointers. The database must then chase each of those 500,000 pointers to fetch the full documents from random disk locations. This chaotic I/O is incredibly slow.

• COLLSCAN: A full collection scan reads data sequentially in large blocks. For large result sets like this, it’s often twice as fast.

Best Practice: Compound indexes are the remedy for low cardinality. Pairing { gender: 1, last_login: -1 } creates high cardinality and instantly makes the index useful for "show me the last 10 active male users."

Covered Queries: The Ultimate Performance Hack

A covered query is the holy grail of read performance. A query is "covered" when all the fields it needs are present in the index itself. The database can satisfy the query entirely from the index without ever touching the actual documents on disk.

This is the difference between a 2ms query and a 0.0ms query under high load.

// Query only for fields in the index
db.users.find(
  { city: "Portland", age: { $gt: 25 } },
  { name: 1, age: 1, city: 1, _id: 0 } // Projection
)

// Index that perfectly covers the query
db.users.createIndex({ city: 1, age: 1, name: 1 })

In an explain() plan, a covered query will have "totalDocsExamined": 0. The data came 100% from the index.

Warning: In MongoDB, a covered query is impossible if the _id field is returned (which it is by default). You must explicitly project it out with _id: 0.

Specialized Indexes for Special Problems

Beyond standard B-Trees, MongoDB offers specialized indexes that elegantly solve unique business problems.

TTL Indexes: Documents with an Expiration Date

A TTL (Time-To-Live) index automatically removes documents after a specified number of seconds. It’s perfect for session data, short-lived tokens, machine-generated logs, or any data that becomes irrelevant after a set time.

A background thread runs every 60 seconds, scans the TTL index for expired dates, and deletes those documents.

// Automatically delete log entries after 1 hour (3600 seconds)
db.session_logs.createIndex(
  { "createdAt": 1 },
  { expireAfterSeconds: 3600 }
)

Critical Rule: The indexed field must contain a BSON date type or an array of dates. TTL indexes cannot be compound indexes, but they can be partial indexes for finer control.

Sparse Indexes: Saving Space on Missing Fields

By default, an index includes an entry for every document in the collection, inserting a null key for documents that lack the indexed field. A sparse index only indexes documents that contain the indexed field.

// Index only users who have provided a phone number
db.users.createIndex(
  { phone_number: 1 },
  { sparse: true }
)

This creates a smaller, faster index if the phone_number field is rare. Crucially, a sparse index is invisible to queries that would need to return documents missing the field, guaranteeing correct results. For example, a sparse index on { x: 1 } will not be used for a { x: null } query.

Partial Indexes: An Unfair Advantage

A partial filter expression indexes only a subset of documents based on a filter condition. This is different from sparse; a partial index is an explicit rule for inclusion, creating the smallest and most efficient index possible for a specific query pattern.

// Index only large, pending orders—the only ones the admin panel queries
db.orders.createIndex(
  { customer_id: 1, order_date: -1 },
  {
    partialFilterExpression: {
      status: "pending",
      total: { $gt: 500 }
    }
  }
)

This index ignores 95% of the orders collection. It’s tiny, stays in RAM forever, and is incredibly fast. The query planner will only use this index if the query’s predicate logically includes the partialFilterExpression.

Text Search Indexes: Your Built-in Search Engine

A text index tokenizes and stems string content for full-text search, handling "runs," "running," and "ran" automatically. It’s simpler than deploying a dedicated search engine like Elasticsearch for basic features.

// Create a text index across multiple fields
db.blog_posts.createIndex(
  { title: "text", body: "text", tags: "text" },
  { weights: { title: 10, tags: 5 } } // Title matches are 10x more relevant
)

// Search for posts about "mongodb indexing"
db.blog_posts.find(
  { $text: { $search: "mongodb indexing" } },
  { score: { $meta: "textScore" } } // Project the relevance score
).sort({ score: { $meta: "textScore" } })

Limitation: A collection can have only one text index, and it can be expensive to create. It’s a fantastic tool but not a replacement for dedicated full-text search solutions at scale.

When NOT to Use an Index: The Cost of Speed

Indexing is not free. It’s a classic space-for-time trade-off with three major costs:

1. Write Performance Degradation: Every INSERT, UPDATE, or DELETE operation must now not only modify the collection but also update every relevant index’s B-Tree. An index on a heavily updated field like last_seen_at can cripple write throughput.

2. Memory Consumption: For optimal performance, your active working set (frequently accessed data and indexes) should fit in the server’s RAM. Each index adds to this burden. A server with 16GB of RAM might perfectly cache a 10GB collection, but adding 6GB of unnecessary indexes will cause disk thrashing and catastrophic performance collapse.

3. Storage Overhead: Indexes consume physical disk space. A collection of 10GB can easily have 3-5GB of indexes. Inefficient or redundant indexes multiply this waste.

When to avoid an index:

• Very small collections (< 1000 documents).

• Fields with extremely low cardinality that are not part of a compound index.

• Tables/Collections that are write-heavy and read-light (e.g., a raw clickstream log that is only analyzed by a batch process).

• A query you run once a month.

Common Indexing Mistakes and Anti-Patterns

These are the issues I see most often in production code reviews.

1. The Shotgun Approach: Creating an index on every field. This is the single worst mistake. You destroy write performance, exhaust RAM, and confuse the query optimizer.

2. Redundant Indexes: Creating { a: 1 } and { a: 1, b: 1 }. The first index is redundant. Any query using { a: 1 } can use the prefix of the second, compound index. Drop { a: 1 }.

3. Ignoring Sort Order in Compounds: Creating { a: 1, b: 1 } for a query that sorts by b descending. This index cannot be used for the sort. The correct index is { a: 1, b: -1 }.

4. Negation Trap: Queries with $ne, $nin, or $not often fail to use indexes effectively. The database must scan the part of the index not matching the value, which can be huge. Reframe your logic when possible.

5. Case-Sensitive Searches Without a Plan: A query for { name: /alex/i } (case-insensitive regex) cannot use a standard index. A common solution is to store a lowercased version of the field in a separate, indexed field.

Best Practices and a Real-World Optimization Walkthrough

Let’s walk through optimizing a slow admin panel query for an e-commerce system.

The Slow Query:
The admin needs to see all "shipped" orders from Chicago in the last quarter, sorted by customer name, showing just the order ID, total, and date.

db.orders.find({
  city: "Chicago",
  status: "shipped",
  order_date: {
    $gte: ISODate("2023-10-01"),
    $lt: ISODate("2024-01-01")
  }
}).sort({
  customer_name: 1
})

Step 1: Analyze and Apply ESR Rule

• Equality: city, status

• Sort: customer_name

• Range: order_date

Step 2: Create the Optimal Index

db.orders.createIndex({
  city: 1,
  status: 1,
  customer_name: 1,
  order_date: 1
})

Step 3: Make it a Covered Query (The Pro Move)
The query only needs _id, total, date, and the sorted customer_name. We can make the index serve all that data by adding total to it. But remember, covered queries are impossible with _id. So we project it away.

// Drop old index and create the covering version
db.orders.createIndex({
  city: 1,
  status: 1,
  customer_name: 1,
  order_date: 1,
  total: 1
})

// The optimized, covered query
db.orders.find({
  city: "Chicago",
  status: "shipped",
  order_date: {
    $gte: ISODate("2023-10-01"),
    $lt: ISODate("2024-01-01")
  }
}, {
  customer_name: 1,
  order_date: 1,
  total: 1,
  _id: 0 // <-- Crucial for covered query
}).sort({
  customer_name: 1
})

Now, the explain("executionStats") output proudly shows "totalDocsExamined": 0 and "totalKeysExamined" exactly matching nReturned. This query is as fast as it will ever be and puts virtually no load on the database.

Frequently Asked Questions

Q: How many indexes is too many?
A: There’s no hard number, but a good rule of thumb for a heavily updated collection is no more than 5-7. Audit them periodically. MongoDB’s $indexStats aggregation can show you which indexes haven’t been used in weeks and are safe to drop.

Q: Can I create an index while my app is running in production?
A: Yes, but do it safely. In MongoDB, creating an index in the foreground blocks all database reads and writes. Always build indexes in the background (this is the default in recent versions, but be explicit) and monitor your replica set’s lag during the process.

Q: Why is my query not using the index I just created?
A: The query planner sees your index, but it deemed another index or a full collection scan as faster. This often happens when an index has terrible cardinality or when a query filter uses a regex that starts with a wildcard /$something/. Check the rejectedPlans section of explain().

Q: What's a multikey index?
A: When you index a field that holds an array, MongoDB automatically creates a multikey index. It creates an index entry for each element in the array. They are powerful but have a key rule: a compound index can only have one multikey field. Two array fields in a single compound index will cause an error.

Conclusion and Next Steps

Database indexing is not a dark art, but a disciplined engineering practice. You’ve moved from the simple "index equals speed" mental model to a deep understanding of B-Trees, compound key ordering, and the profound power of covered queries. The journey from a 30-second query that causes timeouts to a 0.0ms covered query is one of the most satisfying experiences in backend development.

You don’t become an indexing expert overnight. It’s a muscle you build by running explain plans, questioning every COLLSCAN, and gently interrogating your database about how it makes decisions.

Here are your practical next steps:

1. Run explain on a slow endpoint today. Find one COLLSCAN and create a targeted index to fix it.

2. Audit your existing indexes. Look for redundancy using $indexStats and drop indexes that are purely overhead.

3. Model your next feature with indexing in mind. Before writing the code, design the queries and the indexes that will support them.

To go deeper, explore the MongoDB Performance Advisor in Atlas, which automatically flags slow queries and suggests optimal indexes. For SQL-focused developers, learning to read the output of EXPLAIN ANALYZE in PostgreSQL is your equivalent next step.