When to Use Indexes

Indexes are not always good by default.

Use them where they solve real bottlenecks, not as a blanket rule.

Core Concepts

flowchart LR
  A[Observe query workload] --> B{Frequent slow lookups/joins?}
  B -->|Yes| C[Add targeted index]
  B -->|No| D[Keep schema simpler]
  C --> E[Re-test performance and write cost]

Signal	Index likely helpful?
Frequent equality/range filters on large table	Yes
Frequent joins on key columns	Yes
Tiny table with rare queries	Usually no
High-write table with little querying	Often no

Good Candidates

• Columns used often in WHERE
• Foreign key columns used in joins
• Columns used in ORDER BY with selective filters

Poor Candidates

• Very low-cardinality columns queried rarely
• Small static tables where scans are already cheap
• Columns never used in filters or joins

Code Examples

-- Baseline query pattern for decision making.
SELECT order_id, customer_id, total
FROM orders_idx
WHERE customer_id = 1
ORDER BY order_id;

Expected output
Query returns matching customer rows.

-- Add index when this pattern is frequent and table is large.
CREATE INDEX IF NOT EXISTS idx_orders_customer_id
ON orders_idx(customer_id);

Expected output
Index created once and reused by repeated lookups.

-- Review index inventory for the table.
PRAGMA index_list(orders_idx);

Expected output
Lists explicit and auto indexes currently present.

SQLite-Specific Nuances

SQLite Nuance

SQLite's planner chooses whether to use an index per query.

Creating an index does not force usage; it gives the planner an option.

Common Pitfalls / Best Practices

Pitfall

Adding indexes from intuition alone and never measuring impact on reads and writes.

Best Practice

Add one index at a time, validate query benefit, and keep only indexes with clear workload value.

Quick Challenge

A table has 50 rows and mostly receives inserts; you rarely filter by any column. Should you add many indexes now?

View Solution

Usually no.

Start without extra indexes, then add targeted indexes only when real query patterns and data size justify them.