Introduction

A database normally organizes records around a primary key. The primary key provides an efficient way to identify or locate a record using its main identity.

Applications, however, frequently need to retrieve the same data through other attributes.

For example, an online learning platform may need to:

• Find a course by course ID
• List courses by instructor
• List published courses by category
• Find learners by email address
• List enrollments by status and enrollment date
• Find lesson assets by checksum

The primary key cannot directly support every one of these access patterns. A secondary index provides another organized path to the same underlying records.

A secondary index can improve reads, but it is not free. Every relevant insert, update, or deletion in the base data can require corresponding index maintenance.

Prerequisites

Primary Index

A primary index is organized around the main key used to identify records.

{
  "courseId": 42,
  "tenantId": 17,
  "title": "System Design",
  "category": "Software Architecture",
  "instructorId": 81,
  "status": "published",
  "publishedAt": "publication-time"
}

If courseId is the primary key, the direct supported lookup is:

Primary access pattern:

Get course where courseId = 42

The application already knows the primary key, so the database can navigate directly to the required record.

What Is a Secondary Index?

A secondary index stores selected values from the base records under an alternative key.

Base records:

Course 42
    category = Software Architecture

Course 51
    category = Machine Learning

Course 63
    category = Software Architecture


Secondary index by category:

Machine Learning
    -> Course 51

Software Architecture
    -> Course 42
    -> Course 63

The application can now locate courses by category without scanning every course record.

Primary vs Secondary Index

Book-index Analogy

A secondary index works like the index at the end of a book.

Without an index:

Read every page
until the topic is found.


With an index:

Find the topic alphabetically.
Read the listed page numbers.
Open only the relevant pages.

The book index does not contain the full chapter. It contains enough information to locate the chapter.

Similarly, a database secondary index can contain the indexed values, primary-key reference, and optionally selected projected fields.

Query without a Secondary Index

Query:

Find all published courses
for instructor 81.


Available primary key:

courseId


Execution direction:

Read course records
      |
      v
Check instructorId
      |
      v
Check status
      |
      v
Return matching courses

If the database cannot use another suitable access path, it can perform a table or collection scan.

The amount of work can increase with the size of the dataset.

Query with a Secondary Index

Secondary index:

instructorId
    -> status
    -> publishedAt
    -> courseId


Query:

instructorId = 81
status = published
order by publishedAt descending


Execution direction:

Navigate to instructor 81
      |
      v
Select published entries
      |
      v
Read entries in publication order

The secondary index is designed to match the query's filtering and ordering requirements.

Single-field Index

A single-field index organizes records by one attribute.

db.courses.createIndex({
  instructorId: 1
});

This can support queries such as:

db.courses.find({
  instructorId: 81
});

The value 1 represents ascending order in this example. Database-specific syntax and ordering behaviour should be verified in the selected platform.

Compound Secondary Index

A compound index contains more than one indexed field in a defined order.

db.courses.createIndex({
  tenantId: 1,
  instructorId: 1,
  status: 1,
  publishedAt: -1
});

This index is designed around a query such as:

db.courses.find({
  tenantId: 17,
  instructorId: 81,
  status: "published"
}).sort({
  publishedAt: -1
});

Field order matters because the index is sorted first by tenantId, then by instructorId, then by status, and finally by publishedAt.

Compound-index Field Order

A practical design usually considers:

1. Trusted tenant or security scope
2. Equality predicates
3. Sort requirements
4. Range predicates
5. Frequently returned fields, where projection or inclusion is supported

The exact best ordering depends on the database's query planner and index implementation.

Example Query

WHERE:

tenantId = 17
instructorId = 81
status = published
publishedAt >= start-date


ORDER BY:

publishedAt descending

Candidate Index

tenantId
instructorId
status
publishedAt descending

Equality keys narrow the indexed range. The date field then supports the range and ordering requirement.

Left-prefix Principle

A compound ordered index is generally most useful when the query constrains the leading indexed fields.

Compound index:

(tenantId, instructorId, status, publishedAt)


Potentially supported prefixes:

tenantId

tenantId + instructorId

tenantId + instructorId + status

tenantId + instructorId + status + publishedAt

A query filtering only by status may not use this index as effectively because the leading tenant and instructor values are unknown.

Equality and Range Predicates

Equality conditions identify one indexed prefix. A range condition selects a portion of the values following that prefix.

Index:

(tenantId, courseId, activityTime)


Query:

tenantId = 17
courseId = 42
activityTime between Time A and Time B

The database can locate the specific tenant and course range and then scan the ordered activity-time values.

Index-supported Sorting

A secondary index can avoid a separate sorting step when its key order matches the query's filters and ordering.

Index:

(tenantId, category, publishedAt descending)


Query:

Find courses for tenant 17
and category "Database"

Order by publishedAt descending

After navigating to the tenant and category prefix, the index already stores matching entries in publication order.

Covering Index

A covering index contains all the information needed to answer a query. The database can return the result from the index without retrieving the full base record.

Query returns:

courseId
title
publishedAt


Index contains:

tenantId
category
publishedAt
courseId
title


Result:

The index covers the query.

Covering indexes can reduce base-record lookups, but additional projected fields make the index larger and increase maintenance cost.

Relational Example

CREATE INDEX ix_courses_category_date
ON courses
(
    tenant_id,
    category_id,
    published_at DESC
)
INCLUDE
(
    course_id,
    course_title
);

The INCLUDE syntax is database-specific. Some NoSQL databases use index projection options instead.

Index Projection

In distributed NoSQL systems, an index can copy selected attributes from the base records into the index.

Base course item:

courseId
tenantId
category
title
description
instructorId
status
publishedAt
contentLength


Secondary index projection:

category
publishedAt
courseId
title
status

A smaller projection reduces index storage and write volume. A larger projection can avoid fetching the base record.

Base-record Lookup

If the secondary index does not contain all requested fields, the database or application retrieves the full record using the primary key stored in the index entry.

Secondary-index lookup
      |
      v
Find matching primary keys
      |
      v
Retrieve corresponding base records
      |
      v
Return requested fields

This additional step can be called a base-table lookup, document fetch, key lookup, or bookmark lookup, depending on the database.

Write Maintenance

Every applicable base-record change can require secondary-index updates.

Insert course
      |
      +-- Write base record
      +-- Update category index
      +-- Update instructor index
      +-- Update publication-date index
      +-- Update status index

An update can require removing the old index entry and creating a new one.

Before:

status = draft


After:

status = published


Index maintenance:

Remove draft entry
Add published entry

Write-amplification Intuition

Suppose one base record is maintained by three secondary indexes.

Logical application write:

1 base-record update


Potential maintained structures:

1 base record
3 secondary indexes
Transaction or recovery log
Replication structures

A simplified logical index-maintenance estimate is:

\[ MaintainedStructures = 1 + ApplicableSecondaryIndexes \]

This is not a physical write-amplification formula. Actual physical writes depend on the storage engine, batching, logging, compaction, page updates, replication, and durability settings.

Secondary Indexes on B-Trees

A B-Tree-oriented secondary index commonly stores indexed column values in sorted order together with a reference to the base row.

Secondary index:

(category, publishedAt, primaryKey)


Entries:

Database | 2026-09-01 | Course 42
Database | 2026-08-10 | Course 51
NoSQL    | 2026-09-12 | Course 63

Insert, update, or delete operations modify the corresponding index pages. Page splits can occur when an index page lacks free space.

Secondary Indexes on LSM-Trees

In an LSM-oriented engine, a secondary index can have its own memory structures, sorted files, compaction activity, caches, and obsolete versions.

Base-record write
      |
      +-- Base-table WAL and memtable
      |
      +-- Secondary-index memtable
      |
      +-- Index flush
      |
      +-- Index compaction

Additional indexes can multiply write and compaction pressure. Index freshness and consistency depend on the selected database's maintenance contract.

Global Secondary Index

In a partitioned NoSQL database, a global secondary index can use a partition key different from the base table's partition key.

Base table:

Partition key:
courseId


Global secondary index:

Partition key:
instructorId

Sort key:
publishedAt

This supports retrieving courses by instructor even though the base table is organized by course.

Redistribution

Base table distribution:

courseId determines partition


Secondary index distribution:

instructorId determines partition


One base write can require:

- Base partition update
- Index partition update
- Network movement
- Replication of both structures

A global secondary index introduces its own partition-key distribution and can have hot partitions independently from the base table.

Local Secondary Index

A local secondary index retains the base table's partition key but provides a different ordering or sort key within that partition.

Base table:

Partition key:
learnerId

Sort key:
courseId


Local secondary index:

Partition key:
learnerId

Alternative sort key:
enrolledAt

The base table supports locating one learner's enrollment by course. The local index supports listing the same learner's enrollments by enrollment time.

Global vs Local Secondary Index

These terms are commonly associated with distributed key-value and document databases. Exact creation rules, limits, throughput behaviour, and consistency guarantees are provider-specific.

Hot Secondary-index Keys

A well-distributed base table can still have a poorly distributed secondary index.

Base partition key:

courseId


Distribution:

Many different course IDs


Secondary index partition key:

status


Possible values:

draft
published
archived


Problem:

Most records use "published",
creating concentrated index traffic.

Low-cardinality values such as status, country, Boolean flags, or a small number of categories can produce hot secondary-index partitions in a distributed system.

Possible Design Responses

• Combine the value with a higher-cardinality tenant or entity field
• Add a controlled time bucket
• Add a calculated shard suffix
• Create a query-specific aggregate
• Use a different datastore for global analytical queries
• Redesign the access pattern

Adding shards makes writes easier to distribute but requires reads to query and merge several shard ranges.

Sparse Secondary Index

A sparse secondary index contains only records that have the indexed attribute or meet an index condition.

Course records:

Course 42:
featuredAt = publication-time

Course 51:
featuredAt = absent

Course 63:
featuredAt = publication-time


Sparse featured index:

Course 42
Course 63

Sparse indexing can reduce index size and support workflow queues or exceptional states.

Example Access Patterns

• Items waiting for processing
• Failed jobs requiring retry
• Featured courses
• Records with an expiration time
• Assets in quarantine
• Orders with pending payment

Partial Index

A partial index stores entries only for records satisfying a defined predicate.

CREATE INDEX ix_courses_published_category
ON courses
(
    tenant_id,
    category_id,
    published_at DESC
)
WHERE course_status = 'published';

This can make the index smaller when the application frequently queries the indexed subset and the database supports partial indexes.

A query must be compatible with the index predicate for the index to be useful.

Array and Multikey Indexes

Document databases can index values stored inside arrays.

{
  "courseId": 42,
  "tags": [
    "database",
    "system-design",
    "scalability"
  ]
}

db.courses.createIndex({
  tags: 1
});

The index can create entries for individual array values so the application can retrieve documents containing a selected tag.

Large arrays can create many index entries from one document and increase write and storage costs.

Nested-field Index

A document database can support indexes on selected nested fields.

{
  "courseId": 42,
  "instructor": {
    "instructorId": 81,
    "displayName": "Course Instructor"
  }
}

db.courses.createIndex({
  "instructor.instructorId": 1
});

Index the actual nested path used by the query. Indexing the entire embedded object can have different matching behaviour from indexing one nested property.

Unique Secondary Index

Some databases allow a secondary index to enforce uniqueness.

CREATE UNIQUE INDEX uq_learners_tenant_email
ON learners
(
    tenant_id,
    normalized_email
);

The index prevents two learner records in the same tenant from using the same normalized email value.

Uniqueness semantics for missing values, null values, replication, and distributed partitions depend on the selected database.

TTL Index

Some databases provide a time-to-live index or expiration mechanism that identifies records eligible for automatic removal.

{
  "sessionId": "session-identifier",
  "learnerId": 1042,
  "expiresAt": "session-expiry-time"
}

TTL-oriented indexing is suitable for data such as:

• Sessions
• Temporary tokens
• Short-lived caches
• Temporary upload state
• Expiring workflow records

Expiration is not always immediate at the exact timestamp. Verify the database's background deletion behaviour before using TTL as a precise scheduling mechanism.

Index Consistency

A secondary index can be maintained synchronously with the base write or updated asynchronously.

Synchronous Maintenance

Base-record transaction
      |
      +-- Update base data
      +-- Update secondary index
      |
      v
Commit operation

The index and base data participate in the same database operation according to the engine's transaction contract.

Asynchronous Maintenance

Base write acknowledged
      |
      v
Index update queued or replicated
      |
      v
Secondary index catches up
      |
      v
New access path becomes current

During the delay, a query through the secondary index can return an older view or temporarily miss the updated item.

Index-entry Lifecycle

Base record inserted
      |
      v
Index entry created
      |
      v
Indexed field changes
      |
      +-- Old index entry removed
      +-- New index entry created
      |
      v
Base record deleted
      |
      v
Index entry removed or tombstoned
      |
      v
Physical storage reclaimed later
according to engine policy

Deletion and Index Cleanup

Deleting a base record requires the database to remove or invalidate the corresponding entries in every applicable secondary index.

Delete Course 42
      |
      +-- Remove base record
      +-- Remove category index entry
      +-- Remove instructor index entry
      +-- Remove status index entry
      +-- Remove publication-date index entry

LSM-oriented systems can initially write tombstones and reclaim physical index storage during compaction.

Duplicate Secondary-index Values

Secondary-index values are commonly non-unique.

Index key:

category = Database


Matching items:

Course 42
Course 51
Course 63
Course 74

The index requires an additional primary-key or record-identity component to distinguish entries with the same secondary value.

Complete index entry:

(category, publishedAt, courseId)

Deterministic Ordering

When several records have the same sort value, include a stable tiebreaker.

Index:

(tenantId, category, publishedAt, courseId)


Reason:

Two courses can share
the same publication timestamp.


courseId provides
deterministic final ordering.

Deterministic ordering is particularly important for cursor-based pagination.

Pagination through an Index

First page:

tenantId = 17
category = Database

Read first 20 entries
ordered by:

publishedAt descending
courseId descending


Cursor stores:

last publishedAt
last courseId

The next request continues after the final composite index key from the previous page.

Filtering after Index Retrieval

A filter applied after reading index entries does not reduce all index-read work.

Index query retrieves:

1,000 candidate entries


Post-read filter keeps:

10 entries


Problem:

The database still examined
the 1,000 index candidates.

Frequently used selective conditions generally need to participate in the index key or be represented by another suitable access path.

Selectivity

Selectivity describes how narrowly an indexed predicate identifies records.

A simplified selectivity expression is:

\[ Selectivity = \frac{ MatchingRecords }{ TotalRecords } \]

A predicate matching a small portion of the data is generally more selective than one matching most records.

Example

Highly selective:

learnerEmail = one exact email


Low selectivity:

status = published

when nearly every course is published

A low-selectivity index can still help with ordering, covering, or a compound prefix, but an index on the low-cardinality field alone may provide limited benefit.

Query-specific Secondary Index

NoSQL access-pattern design can create a secondary index specifically for one important query.

Access Pattern

List published courses
for one tenant and category,
newest first.

Candidate Index Key

Partition key:

tenantId + category


Sort key:

publishedAt + courseId

Projected Fields

courseId
title
thumbnailKey
difficulty
publishedAt

The index directly supports the required filter, order, and response projection.

Reverse Lookup

A secondary index can reverse the direction of a primary access path.

Primary access:

Course
  -> Learners enrolled in course


Secondary access:

Learner
  -> Courses containing learner enrollment

In some NoSQL designs, the reverse lookup is implemented as a separate query-specific collection or table rather than a database-managed secondary index.

Managed Index vs Materialized Projection

A custom projection is not free. The application must manage updates, retries, duplicate events, stale events, deletions, rebuilds, and reconciliation.

Event-driven Projection

Base transaction commits
      |
      v
Outbox event becomes available
      |
      v
Projection consumer receives event
      |
      v
Update query-specific view
      |
      v
Record source version

Stable record IDs and source versions help the consumer process duplicate or out-of-order events safely.

Index Freshness

An asynchronously maintained index or projection can temporarily lag behind the authoritative base data.

Base record updated at Time A
      |
      v
Index update queued
      |
      v
Index updated at Time B


Between Time A and Time B:

Primary-key read can show new value.
Secondary-index query can show old value.

Define acceptable freshness for:

• New records
• Changed indexed fields
• Deletions
• Publication state
• Tenant ownership
• Access revocation

Security and Tenant Scope

A secondary index must not bypass tenant or authorization boundaries.

Index partition key:

emailAddress


Risk:

Lookup is not explicitly
scoped to the trusted tenant.

Index partition key:

tenantId + normalizedEmail


Trusted tenant ID comes from:

Authenticated server-side context

Indexed fields and projected attributes can expose sensitive information. Protect indexes, backups, logs, monitoring output, and administrative APIs.

Indexing Optional Attributes

Records with missing optional attributes require a defined indexing policy.

{
  "courseId": 42,
  "featuredAt": null
}

Possible database behaviour includes:

• Do not create an index entry
• Create an index entry for null
• Create an index entry only when the field exists
• Apply a partial-index condition

Verify the selected database's handling of absent fields, null values, arrays, and duplicate values.

Building an Index

Creating a secondary index over existing data requires reading base records and constructing corresponding index entries.

Start index build
      |
      v
Read existing records
      |
      v
Extract indexed values
      |
      v
Create and sort index entries
      |
      v
Capture concurrent changes
      |
      v
Validate completed index
      |
      v
Make index available to queries

Index creation can consume CPU, memory, storage bandwidth, temporary space, replication capacity, and transaction-log resources.

Dropping an Index

An unused or redundant index can be removed to reduce maintenance cost.

Before dropping an index:

1. Identify the queries using it.
2. Check index-usage evidence.
3. Check whether it enforces uniqueness.
4. Check whether it supports a sort or covering query.
5. Test query plans without the index where supported.
6. Define a rollback approach.
7. Monitor performance after removal.

Do not drop an index only because recent usage appears low. Some indexes support monthly, quarterly, recovery, or exceptional workflows.

Redundant Indexes

Existing index:

(tenantId, instructorId)


Proposed index:

(tenantId, instructorId, status)


Question:

Can the existing index be extended
or can one index support both
measured access patterns?

Similar indexes can consume duplicate storage and write resources. Consolidation should be validated against every supported query's filtering, ordering, selectivity, and covering requirements.

Query-plan Verification

Creating an index does not prove that the database will use it.

Inspect:

• Index seek or index scan
• Collection or table scan
• Rows or documents examined
• Rows or documents returned
• Base-record lookup count
• Sort operations
• Estimated and actual cardinality
• Logical reads
• Execution time under representative conditions

Conceptual Explain Request

db.courses.find({
  tenantId: 17,
  instructorId: 81,
  status: "published"
}).sort({
  publishedAt: -1
}).explain("executionStats");

Explain syntax and available statistics depend on the database.

Index Effectiveness

A useful comparison is the amount of data examined relative to the amount returned.

\[ ExaminationRatio = \frac{ RecordsExamined }{ RecordsReturned } \]

A very high ratio can indicate poor selectivity, an unsuitable index, post-read filtering, or a query returning only a small portion of examined data.

This ratio should be interpreted together with latency, cache behaviour, query frequency, and storage cost.

When to Add a Secondary Index

Consider adding an index when:

• A frequent important query scans a large dataset
• The query has stable filter or sort fields
• The predicate is sufficiently selective
• The index can support required ordering
• A covering projection can avoid expensive base lookups
• A secondary access pattern is known and measured
• The write and storage cost is acceptable
• No suitable existing index can support the query

When Not to Add an Index

Avoid adding an index only because:

• A query exists but runs rarely
• A monitoring tool suggested it without workload analysis
• The table or collection is small
• The predicate matches most records
• An existing index already supports the access pattern
• The real issue is an inefficient application query
• The workload is heavily write-dominated
• The projected attributes make the index nearly as large as the base data
• The new partition key would create a hotspot

Secondary-index Design Workflow

1. Capture the exact query.
2. Record its frequency and latency objective.
3. List equality predicates.
4. List range predicates.
5. List sort fields and directions.
6. List returned fields.
7. Determine tenant and authorization scope.
8. Estimate selectivity and result size.
9. Check existing indexes.
10. Design candidate key order.
11. Choose projected fields.
12. Estimate write, storage, and partition impact.
13. Create the index in a test environment.
14. Compare execution plans and logical work.
15. Load-test reads and writes.
16. Monitor the index after deployment.

Learning-platform Example

Base Course Record

{
  "tenantId": 17,
  "courseId": 42,
  "categoryId": 9,
  "instructorId": 81,
  "courseTitle": "System Design",
  "courseStatus": "published",
  "difficulty": "beginner",
  "publishedAt": "publication-time"
}

Access Patterns

Conceptual NoSQL Index Definition

{
  "indexName": "courses-by-instructor",
  "partitionKey": [
    "tenantId",
    "instructorId"
  ],
  "sortKey": [
    "courseStatus",
    "publishedAt",
    "courseId"
  ],
  "projection": [
    "courseTitle",
    "difficulty",
    "thumbnailKey"
  ]
}

This is a conceptual model, not vendor-specific configuration. Exact keys, sort behaviour, and projection rules depend on the selected database.

PHP Repository Example

<?php

declare(strict_types=1);

final class CourseRepository
{
    public function __construct(
        private NoSqlClient $client
    ) {
    }

    public function findPublishedByInstructor(
        int $tenantId,
        int $instructorId,
        int $pageSize,
        ?string $cursor
    ): array {
        if ($pageSize < 1 ||
            $pageSize > 100) {

            throw new InvalidArgumentException(
                'The page size is invalid.'
            );
        }

        return $this->client->queryIndex(
            indexName:
                'courses-by-instructor',

            partitionKey: [
                'tenantId' => $tenantId,
                'instructorId' => $instructorId
            ],

            sortConditions: [
                'courseStatus' => 'published'
            ],

            descending:
                true,

            limit:
                $pageSize,

            cursor:
                $cursor
        );
    }
}

NoSqlClient is a conceptual abstraction. Use the official client and query contract of the selected database.

Secondary-index Observability

Useful metrics include:

• Index size
• Index-entry count
• Query count by index
• Index-read latency
• Records examined
• Records returned
• Base-record lookup count
• Index write rate
• Index update failures
• Index replication or freshness lag
• Hot partition activity
• Throttled index operations
• Index-build progress
• Unused or rarely used indexes
• Storage and throughput cost

Alert Conditions

Alert when:

• Index query latency exceeds its objective
• Index freshness lag grows
• Index writes are throttled
• One index partition receives disproportionate traffic
• Index storage grows unexpectedly
• Index maintenance fails
• Base records and index entries diverge
• An index build fails or remains incomplete
• A high-traffic query stops using the expected index

Troubleshooting an Index Query

1. Capture the exact query and parameters.
2. Confirm the expected record exists in the base data.
3. Confirm the relevant indexed attributes exist.
4. Inspect the query plan or index request.
5. Verify the leading index fields match the query.
6. Check equality, range, and sort compatibility.
7. Check whether post-read filtering is occurring.
8. Check selectivity and result cardinality.
9. Check index freshness and replication lag.
10. Check partition distribution and throttling.
11. Check base-record lookup cost.
12. Compare before-and-after read and write measurements.

Common Secondary-index Mistakes

Recommended Test Cases

Secondary-index Best Practices

Practice Exercise

Design secondary indexes for your online learning platform.

Requirements

1. Define the primary key for course records.
2. List published courses by instructor and publication date.
3. List published courses by category and publication date.
4. Find a learner by normalized email within one tenant.
5. List a learner's enrollments by enrollment date.
6. List failed content-processing jobs requiring retry.
7. List temporary sessions by expiration time.
8. Support deterministic cursor pagination.
9. Choose projected fields for every index.
10. Estimate index-entry size.
11. Identify low-cardinality and hot-key risks.
12. Define index consistency requirements.
13. Measure index-query latency.
14. Measure insert, update, and delete overhead.
15. Verify tenant isolation.
16. Document each index and its supported access pattern.

Index-design Template

Frequently Asked Questions