Database indexing strategy
Decision tree for choosing single-column, composite, and specialized indexes.
Most database slowdowns start with a full table scan — your database reads millions of rows to answer one query. This template walks through the decision tree: do you need an index, should it be a single column or composite, and when should you use a partial index to save storage? It captures the diagnostic steps (EXPLAIN PLAN, performance testing) and the rules-of-thumb teams use to decide what to index first.
Index decisions matter because the wrong index wastes storage and slows down writes, while missing the right index cripples query performance. This diagram shows every branch: single vs. composite, the order of columns in a composite index, and the partial-index escape hatch for soft-deleted or archived data.
When to use this template
- Query optimization reviews — when a feature ships and suddenly user searches are slow, walk the team through this diagram to diagnose whether it's a missing index or a query shape problem.
- Schema design discussions — before adding a new column or table, sketch which indexes you'll need and socialize them with the team so everyone agrees on the strategy.
- Database migration planning — when dropping an old table or renaming a column, use this diagram to identify which indexes must move with it.
How to adapt it
Start by naming your real tables and columns. Common extensions:
- Add a full-text search index branch if you support keyword search (in PostgreSQL, use GIN or GIST for tsvector).
- Insert a covering index decision — include extra columns in the index so the database never needs the main table (PostgreSQL INCLUDE, SQL Server INCLUDE, MySQL covered).
- Add a statistics and maintenance step — regular REINDEX and ANALYZE to keep the query planner accurate and the index compact.
Visual edits regenerate clean Mermaid code, so you can adapt this to your database engine — PostgreSQL, MySQL, SQL Server — and document your team's indexing rules without syntax overhead.
Mermaid code
Copy it anywhere Mermaid is supported — GitHub, Notion, or your docs.
flowchart TD
A[Identify slow query] --> B[Profile with EXPLAIN PLAN]
B --> C{Full table scan?}
C -->|No| D[Already indexed]
D --> E[End]
C -->|Yes| F{Single column or multiple?}
F -->|Single column| G[Add single-column index]
G --> H[Test performance]
F -->|Multiple columns| I{Query filters on all columns?}
I -->|Yes| J[Create composite index]
J --> H
I -->|No| K{Range or IN query?}
K -->|Yes| L[Order composite: equality first]
L --> J
K -->|No| M[Consider partial index]
M --> H
H --> N{Faster?}
N -->|No| O[Drop index, investigate further]
O --> E
N -->|Yes| P[Monitor index bloat]
P --> E
Frequently asked questions
- What is a database index and why do I need one?
- An index is a separate, sorted data structure that lets the database find rows without scanning the entire table. Without indexes, every query must read millions of rows; with the right index, the database finds the exact rows in microseconds. They are critical for production performance.
- What is the difference between a single-column index and a composite index?
- A single-column index sorts by one column. A composite index (or multi-column index) sorts by multiple columns in order — e.g., (user_id, created_at). Composite indexes are more powerful: a query filtering on both user_id and created_at uses one index, not two. But they consume more storage and slow down writes.
- How do I know if my index is actually being used?
- Use EXPLAIN PLAN before and after adding the index. The plan should show 'Index Scan' or 'Index Seek' instead of 'Table Scan'. In PostgreSQL, run ANALYZE to update statistics. If the plan doesn't change, the index isn't helping — drop it and investigate the query or schema instead.
- When should I create a partial index instead of a full index?
- A partial index includes only rows matching a WHERE condition — e.g., an index on (user_id) WHERE deleted_at IS NULL. They use less storage and are faster to maintain than full indexes. Use them when a large fraction of rows are permanent (soft-deleted, archived, inactive) and you never query them, so the index only indexes live rows.