---
term: 'Database Index'
seoTitle: 'What is a Database Index? How Indexing Works'
headline: 'What is a Database Index?'
slug: database-index
category: database
shortDefinition: 'A database index is a sorted lookup structure that lets the engine find matching rows directly instead of scanning the whole table.'
relatedTerms:
  - database-queries
  - n-plus-one-query-problem
  - database-schema
  - multi-tenant-database-architecture
contrastsWith:
  - database-queries
faq:
  - question: 'What is a database index in simple terms?'
    answer: 'A separate, sorted structure — usually a B-tree — holding column values plus pointers to their rows, exactly like a book''s index holds terms plus page numbers. The engine looks up the value in the small sorted structure and jumps straight to the matching rows, instead of reading the entire table hoping to find them.'
  - question: 'How much faster is an indexed query?'
    answer: 'The difference between logarithmic and linear work: a B-tree lookup touches a handful of pages whether the table holds ten thousand rows or a hundred million, while a full scan reads everything. At small sizes both feel instant — which is why missing indexes hide in development and detonate in production, where the same query suddenly examines millions of rows.'
  - question: 'Do indexes slow down writes?'
    answer: 'Yes — that is the tax. Every insert, update, or delete on an indexed column must also update each index that references it: six indexes on a table means up to six extra structure updates per write, plus the storage they occupy. Indexes are a read-speed purchase paid for in write speed and disk — deliberate ones earn it, forgotten ones just bill you.'
  - question: 'Which columns should be indexed?'
    answer: 'The columns your queries actually use: filters (WHERE), join keys — including foreign keys and pointer fields, which some engines do not index automatically — and sort columns (ORDER BY). Add selectivity to the test: an email column distinguishing millions of rows earns its index; a boolean flag that splits the table in half mostly does not.'
  - question: 'What order should composite index columns be in?'
    answer: 'Equality first, range and sort later — and remember the leftmost-prefix rule: an index on (a, b, c) serves queries filtering on a, on a and b, or on all three, but not on b alone. The document-database phrasing of the same rule is ESR: Equality, Sort, Range. Column order is the difference between a composite index working and merely existing.'
  - question: 'How do I find missing or unused indexes?'
    answer: 'For missing ones: run the query plan — EXPLAIN — and look for full scans on large tables; the filter column of a slow, frequent query is the candidate. For unused ones: every engine tracks index usage statistics, and an index no query has touched in months is pure write tax — drop it. The plan and the stats together are the whole methodology.'
  - question: 'What are unique, partial, and covering indexes?'
    answer: 'Specializations of the same structure: a unique index enforces no-duplicates as a constraint while speeding lookups; a partial index covers only rows matching a condition — small and fast for hot subsets like unshipped orders; a covering index contains every column a query needs, letting the engine answer from the index alone without visiting the table.'
  - question: 'Does indexing work the same in document databases?'
    answer: 'Conceptually identical — B-trees over field values — with the same trade-offs and the same leftmost-prefix logic under the ESR rule of thumb. Document stores add multikey indexes over array fields and geospatial variants, and the operational rule survives translation: index what you query, especially the pointer fields that joins and includes traverse.'
codeLanguages: [javascript, dart, swift, kotlin]
externalAuthorities:
  - name: 'PostgreSQL index types documentation'
    url: 'https://www.postgresql.org/docs/current/indexes-types.html'
  - name: 'Database index (Wikipedia)'
    url: 'https://en.wikipedia.org/wiki/Database_index'
  - name: 'MongoDB indexes documentation'
    url: 'https://www.mongodb.com/resources/basics/databases/database-index'
  - name: 'PostgreSQL — Using EXPLAIN'
    url: 'https://www.postgresql.org/docs/current/using-explain.html'
cta:
  title: 'Fast queries, visible indexes'
  text: 'Back4app puts index management where the schema lives: create and review indexes per class in the dashboard, on the same MongoDB-backed store your queries hit — no migration scripts, no guessing which fields your filters need.'
  linkText: 'Start Free'
  linkUrl: 'https://www.back4app.com/signup'
author: 'Back4app Engineering'
publishedDate: '2026-07-26'
translationKey: database-index
---

**A database index is a sorted lookup structure that lets the engine find matching rows directly instead of scanning the whole table.** The book analogy is exact: nobody finds "idempotency" in a 900-page book by reading it — they check the index and jump to the page. Databases make the same choice on every query, and whether they *can* jump is the single most common difference between a 5-millisecond query and a 5-second one.

## Key takeaways

| Question | Answer |
| --- | --- |
| What it is | A sorted side-structure: values + pointers, like a book's index |
| The payoff | Logarithmic seek instead of linear scan — decisive at scale |
| The tax | Every write updates every index; storage grows |
| What to index | Filter, join/pointer, and sort columns — if selective |
| The diagnostic | EXPLAIN: a full scan on a big table is the tell |

## The index, created and felt

```sql
-- The workhorse: a composite index shaped like the hot query
CREATE INDEX orders_pending ON orders (status, created_at);

-- Specializations of the same idea:
CREATE UNIQUE INDEX users_email ON users (email);         -- constraint + speed
CREATE INDEX unshipped ON orders (created_at)
  WHERE shipped = false;                                  -- partial: hot subset only

-- The before/after, via the plan:
EXPLAIN SELECT * FROM orders WHERE status = 'pending' AND created_at > now() - interval '1 day';
--  before:  Seq Scan on orders   (rows examined: 4,812,309)
--  after:   Index Scan using orders_pending   (rows examined: 1,214)
```

Application code meets the same physics through query shape — this query *is* the index specification `(status, createdAt)`, whatever surface writes it:

**JavaScript:**

```javascript
// JavaScript / Node.js — Back4app JS SDK
// The query shape tells you the index: (status, createdAt)
const query = new Parse.Query('Order');
query.equalTo('status', 'pending');       // equality first…
query.greaterThan('createdAt', since);    // …then the range
query.descending('createdAt');            // …sorted by the same column
const backlog = await query.find();
// Indexed: milliseconds at any size. Unindexed: a full collection scan.
```

**Flutter:**

```dart
// Flutter / Dart — Back4app Flutter SDK
// The query shape tells you the index: (status, createdAt)
final query = QueryBuilder<ParseObject>(ParseObject('Order'))
  ..whereEqualTo('status', 'pending')      // equality first…
  ..whereGreaterThan('createdAt', since)   // …then the range
  ..orderByDescending('createdAt');        // …sorted by the same column
final response = await query.query();
// Indexed: milliseconds at any size. Unindexed: a full collection scan.
```

**Swift:**

```swift
// iOS / Swift — Back4app Swift SDK
// The query shape tells you the index: (status, createdAt)
let query = Order.query("status" == "pending", "createdAt" > since)
  .order([.descending("createdAt")])
query.find { result in
  if case .success(let backlog) = result { render(backlog) }
}
// Indexed: milliseconds at any size. Unindexed: a full collection scan.
```

**Kotlin:**

```kotlin
// Android / Kotlin — Back4app Android SDK
// The query shape tells you the index: (status, createdAt)
val query = ParseQuery.getQuery<ParseObject>("Order")
query.whereEqualTo("status", "pending")     // equality first…
query.whereGreaterThan("createdAt", since)  // …then the range
query.orderByDescending("createdAt")        // …sorted by the same column
query.findInBackground { backlog, e -> if (e == null) render(backlog) }
// Indexed: milliseconds at any size. Unindexed: a full collection scan.
```

## How the jump works

```mermaid
flowchart TB
  accTitle: A B-tree index lookup
  accDescr: A query descends a shallow sorted tree from the root through a branch node to a leaf holding the matching value and row pointers, touching a handful of pages instead of scanning the entire table.
  R["Root node<br/>(ranges)"] --> B1["Branch<br/>A–M"]
  R --> B2["Branch<br/>N–Z"]
  B2 --> L1["Leaf: 'pending' → rows 88, 1042, 55913"]
  B1 --> L2["Leaf …"]
  L1 --> T["Fetch exactly those rows"]
```

The default structure everywhere is the **B-tree**: shallow, sorted, self-balancing — three or four levels cover hundreds of millions of entries, and its leaves are linked, which is why one structure serves equality, ranges, and `ORDER BY` alike. That versatility is why "when in doubt, B-tree" is the standing advice, with the alternatives as specialists.

## B-tree vs. hash vs. the specialists

### Which index type should you use?

| Type | Supports | Reach for it when |
| --- | --- | --- |
| B-tree (default) | `=`, `<`, `>`, ranges, sorts, prefixes | Almost always — the generalist |
| Hash | Equality only, O(1) | Exact-match lookups, nothing else |
| Composite | Multi-column, leftmost-prefix | The hot query filters on several columns |
| Unique | B-tree + no duplicates | Constraint and index in one |
| Partial | A filtered slice of rows | Hot subsets: unshipped, unread, active |
| Covering | Query answered from index alone | Read-heavy queries with a stable column list |
| Full-text (inverted) | Words → documents | Search boxes |
| Geospatial | Points, regions, distance | "Near me" queries |

Two rules carry the composite row: the **leftmost-prefix** rule — an index on `(a, b, c)` serves `a`, `(a, b)`, `(a, b, c)`, never `b` alone — and **equality first, range/sort last** in column order (the ESR mnemonic in [document-database indexing](https://www.mongodb.com/resources/basics/databases/database-index), where the same B-trees do the same work).

## When to index — and when not to

**Index:** columns in frequent `WHERE` filters; join keys and pointer fields (some engines never index foreign keys automatically — a classic silent scan); `ORDER BY` columns on hot paths; and always with selectivity in mind — an email column (millions of distinct values) earns its keep, a status flag (three values) usually doesn't alone, though it shines *leading a composite* with a range behind it. **Don't index:** small tables the engine scans faster than it seeks; write-hot tables beyond the essential few; columns already served by an existing index's left prefix; and anything you can't name a query for — an index without a query is pure write tax. The audit loop: [EXPLAIN](https://www.postgresql.org/docs/current/using-explain.html) the slow queries for scans, check usage stats for dead indexes, add and drop accordingly.

## Common use cases

- **The hot-list query.** Status + date filters behind every dashboard and feed — the composite index's home turf.
- **Login and lookup fields.** Email, username, external IDs — unique indexes doing constraint and speed at once.
- **Join and pointer paths.** Every foreign key and pointer your queries traverse; the N+1 problem's quiet accomplice is an unindexed one.
- **Multi-tenant filters.** Tenant-leading composites — the [multi-tenant architecture rule](/glossary/multi-tenant-database-architecture/) that every index starts with `tenant_id`.
- **Search and geo.** Inverted and spatial indexes powering the queries B-trees can't.

## Should you add that index? A decision matrix

| Add it when… | Skip it when… |
| --- | --- |
| A frequent query filters or sorts on the column | No production query uses it |
| EXPLAIN shows scans on a growing table | The table is small and stays small |
| The column is selective (many distinct values) | An existing index's prefix already covers it |
| It's a join key or pointer field | The table is write-dominated and the read is rare |
| A unique rule needs enforcing anyway | You're guessing — measure first |

The discipline in one sentence: indexes are created in response to queries, reviewed against usage, and dropped without sentimentality.

## Limitations and trade-offs

- **Writes pay for reads.** Every index is another structure each write must maintain — bulk loads famously run faster with indexes dropped and rebuilt.
- **Storage is real.** Indexes commonly rival the table's own size; covering indexes especially trade disk for speed.
- **The optimizer decides, not you.** A low-selectivity index may be rightly ignored; stale statistics can wrongly ignore a good one — the plan is the truth.
- **Order mistakes neutralize composites.** `(created_at, status)` and `(status, created_at)` are different tools; the leftmost-prefix rule forgives nothing.
- **Indexes can't fix the query.** Leading wildcards, functions over columns, and N+1 loops defeat indexing from above — query shape comes first.

## Indexes on Back4app

Back4app is an open-source Backend-as-a-Service (BaaS) platform that combines a managed database, auto-generated REST and GraphQL APIs, authentication, file storage, and Cloud Code serverless functions. Index management lives with the schema in the dashboard: create, review, and drop indexes per class — single-field or compound — on the MongoDB-backed store your SDK queries actually hit, following the same B-tree and ESR logic above. The query in the code tabs and the index that serves it are designed in the same place, which is precisely where the "index what you query" discipline wants to live.