esc
  • WorkflowsPages
  • Case studiesPages
  • Machine Learning FoundationsLearn
  • AI EngineeringLearn
  • AI Guardrails & Red TeamingLearn
  • UI/UXLearn
  • Design SystemsLearn
  • Production System DesignLearn
  • Book a callContact
  • Download ResumeContact
Let’s talk

I’ll build the agent for it.

Index Table: A Second Table So Queries Can Skip Full Scans

How an extra table, keyed by the field you search on, lets a partitioned data store answer questions it cannot answer quickly on its own, and what that extra table costs.

In short

An index table is an extra table keyed by the field you search on, so a partitioned data store can answer queries without scanning every partition.

11 min readFree

What you will learn

This lesson teaches the Index Table pattern. The idea is simple. You build a second table that is organized by a field you search on, so a question about that field does not have to read every row in the database. The pattern is common in NoSQL stores and partitioned databases, where the main lookup key is the only fast path.

By the end you will know why the pattern is needed, the three common ways to build one, what it costs, and when to skip it. A worked example follows an online store's My Orders screen step by step.

Key words

Primary key
The main field that identifies each row, such as an order ID.
Partition
A slice of a table stored on its own machine or group of machines.
Partition key
The field that decides which partition a row is placed in.
Full scan
Reading every row in every partition to find the few rows you want.
Index table
An extra table keyed by a different field, used to find rows quickly.
Fact table
The original table that holds the authoritative data.
Denormalize
Store the same data in more than one place on purpose, to speed up reads.
Drift
When a copy or index no longer matches the data it was built from.

Why do plain lookups fall short?

A NoSQL store spreads its rows across partitions using a partition key. When your question includes that key, the store knows which partition to visit, so the lookup is quick. The speed comes from knowing where to look. Other stores in the same family work the same way, and the same rule applies to them.

The problem starts when a question uses a different field. Imagine a users table partitioned by user ID, and a question that asks for the account with a certain email address. The store has no map from email address to partition. It must check each partition, and that gets slower as the table grows.

What are full scans across partitions?

A full scan is not one slow step. It reads rows in every partition, and each machine that holds a partition has to do its share of the reading. The cost grows with the size of the data, not with the number of rows that match. Even a single matching account costs a read of everything. That is why a search by a non-key field can feel fine in a small test and then fail in production.

What are three ways to build an index table?

The pattern is a family of designs rather than one fixed table. Most teams pick one of three common shapes. Each one trades read speed against storage and effort, so choose by asking how often the query runs and how often the data changes.

  • Pointer-only index

    Holds just the search field and the primary key of the matching row. A lookup finds the key first, then reads the main table. It is small and cheap to keep, but it takes two lookups.

  • Copied-data index

    Holds full or partial copies of the data, organized by the new key. A lookup needs one read, so it is the fastest. The cost is more storage, and every change must update each copy.

  • Composite-key index

    Joins several fields into one key, such as town and last name, so a question about both fields becomes one range lookup. The key must be encoded so that different combinations never look the same.

Pointer-only indexes are the simplest. The searched field becomes the index key, and the value stored is the primary key of the source row. Some databases keep this same kind of pointer inside their own secondary index. Either way, the reader pays a second step to fetch the full record, but the index stays small.

Keys only: the index points back

A pointer-only table does not hold the customer's name, town, or other details. It holds the search value and the key that leads back to the source row. When the source record changes in a way that does not touch the search field, the pointer usually stays valid, so there is less work to keep the index right.

Full or partial copies: faster reads

A copied-data index repeats the whole record under the new key. A partial copy repeats only the fields the screen reads most often, and points back to the source for everything else. Partial copies sit in the middle. They suit a screen that shows a few small, stable fields, because the read is fast and the copy stays manageable.

Composite keys: several fields in one key

When queries filter on two or more fields together, build the index key from all of them, in a fixed order. Town can come first and last name second, so all people in one town sit together and can be read as one block. A hierarchy, such as country, then region, then city, follows the same idea. Keep the boundaries between parts clear. Without a clear separator, a value like 'ab' followed by 'c' could look the same as 'a' followed by 'bc'.

Worked example: the My Orders screen

An online store keeps every order in a NoSQL database, partitioned by order ID. That suits the order page, which fetches one order by its ID. The My Orders screen is different. It must show all orders for one customer. Without help, the app would scan every order partition and keep only the rows for that customer. The team therefore adds an index table, keyed by customer ID, that lists each customer's order IDs.

  1. 1

    Name the question

    The screen asks for orders by customer ID. Customer ID is not the partition key of the orders table, so a plain lookup cannot answer the question.

  2. 2

    Design the index table

    Create a table whose partition key is the customer ID. Each row holds one customer ID and the list of that customer's order IDs. This is a pointer-only index, because order details are large and change often.

  3. 3

    Fill it from existing orders

    Run a one-time job that reads the existing orders and writes one index entry for each order. This builds the first version of the index.

  4. 4

    Keep it in sync

    When a new order is saved, the app also adds the order ID to that customer's index row. When an order is cancelled, the app removes it. A single transaction cannot span the orders table and the index table, so the app must handle a failure between the two writes, for example by retrying the index step.

  5. 5

    Read the screen

    The app reads one index row for the customer, gets the order IDs, and fetches each order by its primary key. That is one read plus one read per order, with no scan of the other partitions.

The sample below shows the same idea with plain Python dictionaries. It counts how many rows each method reads, so you can compare the two approaches.

Python
orders = {
    'p1': [{'order': 'o1', 'customer': 'c7'}, {'order': 'o2', 'customer': 'c3'}],
    'p2': [{'order': 'o3', 'customer': 'c7'}, {'order': 'o4', 'customer': 'c9'}],
}

# Full scan: read every row in every partition.
reads = 0
found = []
for rows in orders.values():
    for row in rows:
        reads += 1
        if row['customer'] == 'c7':
            found.append(row['order'])
print('scan:', found, 'rows read:', reads)

# Index table: customer ID -> list of order IDs.
index = {}
for rows in orders.values():
    for row in rows:
        index.setdefault(row['customer'], []).append(row['order'])
print('index:', index['c7'], 'lookups:', 1)

Output

scan: ['o1', 'o3'] rows read: 4
index: ['o1', 'o3'] lookups: 1

Tip

Build the index from the same rows the store holds, and keep the update code next to the code that writes orders. Now and then, compare the index answer with a full scan to check that the two still agree.

What does the pattern cost?

An index table is not free. It buys faster reads with three kinds of cost. Each cost grows as the data changes more often or the copies get larger.

  • Extra storage

    The index takes space. A copied-data index can take a lot of it, because the same record exists twice.

  • Extra writes

    Each change to the source data may also write to every index table. Those writes use capacity you pay for, so a busy table makes every save more expensive.

  • Consistency risk

    The index can lag behind the source or drift away from it. A customer may briefly miss a new order, or still see an order that was already cancelled.

How do you keep the index in sync?

Someone must own the index. The application can update it in the same request as the write, which keeps the two close together but adds work to each save. A background process can also update it later. That is easier on the user, but it means a short window in which results can be stale. Pick the approach that matches how much stale data the screen can tolerate, and write the rule down.

Tip

Treat the source table as the truth. When the index and the source disagree, trust the source and rebuild the index row from it. A repair job that does this on a schedule is a cheap safety net.

Check your understanding

Quick check

A users table is partitioned by user ID. The login screen must find an account by email address in milliseconds, and searching every partition is too slow. You add an index table. What should it contain, and what must happen when a user changes their email?

When not to use it

Skip the pattern in a few clear cases. First, check whether the store already gives you what you need, because a built-in feature is usually easier to run than a table you maintain yourself.

  • The store has secondary indexes

    Some databases maintain a secondary index for you. Cassandra, for example, creates a hidden table when you define a regular secondary index. Azure Table Storage indexes only its two main keys, so a store with real secondary indexes may remove the need for a manual table.

  • The data changes very often

    Each change must reach every index, and the index can go stale quickly. When the upkeep costs more than the reads you save, the pattern loses.

  • Only a few items are searched

    An index over all items helps less when a small share of items gets most of the queries. A cache may fit that case better.

  • The query is rare

    A search that runs once a day may be fine as a scan. A new table adds storage, writes, and risk for little gain.

Quick check

A product catalog sits in a database that offers built-in secondary indexes on any column. Prices change many times a day, and searches by brand are rare. What is the best first move?

Key takeaways

An index table is a second table keyed by the field you search on. It turns a full scan into one or two direct lookups. It comes in pointer-only, copied-data, and composite-key forms, and each form trades read speed for storage and upkeep. Use it when queries on a non-key field are frequent and the store cannot answer them well itself. Write down who keeps it in sync, and check it against the source now and then. Precomputed answers, caches, and event logs are related but separate ideas, and they have their own lessons.

Sources

How is this lesson?

Last updated on 18 November 2026

Learn with me

Every lesson is free. I add new ones as I learn. Have a question, or want help with your own work? Book a short call.