What is a 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

The index, created and felt

-- 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 / 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 — 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.

// 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.

// 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

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?

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, 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 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 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

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.