Indexing in NoSQL

A MongoDB collection with 500 million documents and no index on the field you are filtering by will scan every single document — 500 million reads — to return 20 results. Add the right index and that same query scans 20 entries. The difference is not incremental. It is the difference between a query that takes 8 minutes and one that takes 2 milliseconds. Indexing is the single highest-leverage performance tool in any NoSQL database, and most teams use it badly.

Indexes Are a Trade-off, Not a Free Lunch

Every index you create speeds up reads on the indexed field and slows down every write to that collection. On every insert, update, and delete, MongoDB must update not just the document but every index that covers that document. A collection with 8 indexes requires 9 write operations per document change — one for the document, eight for the indexes. At high write throughput, an over-indexed collection becomes the bottleneck.

Index Types Across NoSQL Databases

Different NoSQL databases offer different index types. Understanding what each one does — and what it costs — prevents the common mistake of reaching for the most powerful type when a simpler one would do.

Compound Indexes — The ESR Rule

In MongoDB, compound indexes cover multiple fields in a single B-tree. The order of fields in a compound index is critical — it determines which queries the index can serve. The ESR rule is the practical guide: put Equality fields first, then Sort fields, then Range fields last.

Hands-on — Diagnosing and Fixing Slow Queries

The scenario: You are the backend engineer for a job board platform. The jobs listing page filters by city and job_type, then sorts by posted_at descending. There are 40 million job documents. The page is taking 6 seconds to load. Your first job is to understand why, then fix it.

// Step 1: diagnose with explain() — winningPlan reveals what MongoDB is doing
db.jobs.find(
  { city: "London", job_type: "Engineering" }
).sort(
  { posted_at: -1 }
).explain("executionStats")

{
  winningPlan: {
    stage: "SORT",
    inputStage: {
      stage: "COLLSCAN",       ← scanning all 40 million documents
      filter: { city: "London", job_type: "Engineering" }
    }
  },
  executionStats: {
    executionTimeMillis: 6240,
    totalDocsExamined:   40000000,   ← 40M docs scanned
    nReturned:           847          ← 847 results returned
  }
}

The scenario continues: You apply the ESR rule — two equality fields (city, job_type) first, then the sort field (posted_at) last. You create the index and re-run the query.

// Create the compound index following ESR order
// E: city (equality)
// E: job_type (equality)
// S: posted_at (sort — descending matches query sort direction)
db.jobs.createIndex(
  { city: 1, job_type: 1, posted_at: -1 },
  { name: "jobs_city_type_date_idx" }
)

"jobs_city_type_date_idx"
Index built successfully — scanning 40M documents to build index.
Build time: 48 seconds (one-time cost)
Index size on disk: 1.2 GB

// Re-run explain() after creating the index
db.jobs.find(
  { city: "London", job_type: "Engineering" }
).sort(
  { posted_at: -1 }
).explain("executionStats")

{
  winningPlan: {
    stage: "FETCH",
    inputStage: {
      stage: "IXSCAN",         ← index scan (not collection scan)
      indexName: "jobs_city_type_date_idx",
      direction: "forward"
    }
  },
  executionStats: {
    executionTimeMillis: 2,      ← 6240ms → 2ms
    totalDocsExamined:   847,    ← exactly the docs returned
    nReturned:           847,
    totalKeysExamined:   847     ← 1:1 examination ratio ✓
  }
}

Secondary Indexes in Cassandra — Use With Caution

Cassandra's secondary indexes are fundamentally different from MongoDB's. In MongoDB, an index is a separate B-tree structure stored on the same node as the data. In Cassandra, a secondary index is a distributed index — each node maintains a local secondary index only for the data it owns. A query on a secondary index must hit every node in the cluster and merge results — a scatter-gather operation.

The scenario: Your team has a Cassandra table of user sessions partitioned by user_id. The security team needs to quickly find all active sessions for a specific device fingerprint — without scanning all users. A secondary index on device_fingerprint would scatter across all nodes. The right solution is a dedicated lookup table.

-- WRONG approach: secondary index on device_fingerprint
-- Hits every node in the cluster, merges results — slow at scale
CREATE INDEX ON user_sessions (device_fingerprint);  -- avoid this

-- RIGHT approach: a dedicated lookup table for this query pattern
-- "Find all sessions for a given device fingerprint"
CREATE TABLE sessions_by_device (
  device_fingerprint TEXT,
  created_at         TIMESTAMP,
  session_id         UUID,
  user_id            TEXT,
  ip_address         TEXT,
  PRIMARY KEY (device_fingerprint, created_at, session_id)
) WITH CLUSTERING ORDER BY (created_at DESC, session_id ASC)
  AND default_time_to_live = 86400;  -- sessions expire after 24h

Created table sessions_by_device
Took 0.612 seconds

Query: "all active sessions on device fp_XK99"
  SELECT * FROM sessions_by_device
  WHERE device_fingerprint = 'fp_XK99'
  LIMIT 10;

  Hits: 1 partition on 1 node  ✓
  Query time: 1.8ms

DynamoDB Global Secondary Indexes

DynamoDB's Global Secondary Index (GSI) is the DynamoDB equivalent of a separate query table — it projects a copy of selected attributes under a different partition key, allowing you to query the same data by a completely different attribute. Unlike Cassandra's secondary indexes, a GSI is maintained automatically by DynamoDB and provides single-partition performance for the indexed query.

The scenario: Your DynamoDB orders table is partitioned by customer_id. The warehouse team needs to query orders by warehouse_id to plan daily pick lists. This is a completely different access pattern — you add a GSI on warehouse_id.

import boto3

dynamodb = boto3.client('dynamodb', region_name='eu-west-1')

# Add a GSI to an existing table — DynamoDB backfills it automatically
dynamodb.update_table(
    TableName='orders',
    AttributeDefinitions=[
        {'AttributeName': 'warehouse_id', 'AttributeType': 'S'},
        {'AttributeName': 'order_date',   'AttributeType': 'S'}
    ],
    GlobalSecondaryIndexUpdates=[{
        'Create': {
            'IndexName': 'warehouse-orders-idx',
            'KeySchema': [
                {'AttributeName': 'warehouse_id', 'KeyType': 'HASH'},
                {'AttributeName': 'order_date',   'KeyType': 'RANGE'}
            ],
            'Projection': {'ProjectionType': 'INCLUDE',
                           'NonKeyAttributes': ['status', 'item_count']},
            'ProvisionedThroughput': {'ReadCapacityUnits': 10,
                                      'WriteCapacityUnits': 10}
        }
    }]
)

GSI 'warehouse-orders-idx' creation initiated.
DynamoDB is backfilling the index from existing table data.
Status: CREATING → ACTIVE (typically 5-30 minutes for large tables)

Query via GSI:
  warehouse_id = 'wh_london_3'
  order_date between '2025-01-15' and '2025-01-15'
  → 142 orders returned in 4.1ms  ✓

TTL Indexes — Automatic Data Expiry

The scenario: Your platform stores user sessions in MongoDB. Sessions older than 30 days must be deleted. A nightly cron job was running deleteMany({"{"} created_at: {"{"} $lt: thirtyDaysAgo {"}"} {"}"}) — but it was locking the collection for 40 seconds at 2 AM and causing latency spikes. You replace the cron job with a TTL index.

// TTL index — MongoDB auto-deletes documents after 30 days
// The background thread runs every 60 seconds checking for expired docs
db.sessions.createIndex(
  { created_at: 1 },
  { expireAfterSeconds: 2592000 }  // 30 days in seconds
)

// Verify the TTL index was created
db.sessions.getIndexes()

[
  { v: 2, key: { _id: 1 }, name: '_id_' },
  {
    v: 2, key: { created_at: 1 },
    name: 'created_at_1',
    expireAfterSeconds: 2592000
  }
]

TTL monitor running every 60 seconds.
Expired documents deleted in small batches — no collection lock.
No more 2 AM latency spikes ✓

Practice Questions — You're the Engineer

Quiz — Indexing in Production