When to Index — and When Not To

Indexes are not free

Every index slows down writes (every INSERT/UPDATE/DELETE must update the index) and takes disk space. The art is creating exactly the indexes that pay back more than they cost.

Index when:

The column is in a frequent WHERE, JOIN, or ORDER BY on a large table.
The column has high selectivity (many distinct values).
It's a foreign key — almost always index FKs.

Don't index when:

The table is tiny (< a few thousand rows). Full scan is faster than the index lookup overhead.
The column has low selectivity (e.g., a boolean). Use a partial index instead.
The column is rarely filtered or joined on.
The table is write-heavy and the index won't be used by hot queries.

Warning: Indexes are an active maintenance burden. Reviewing existing indexes (which are unused, which are duplicated, which were added to fix a bug from two years ago) is part of normal database hygiene. SELECT name FROM sqlite_schema WHERE type='index' AND tbl_name='X' is the audit starting point.

-- Every user-created index on this table SELECT name, sql FROM sqlite_schema WHERE type = 'index' AND tbl_name = 'messages' AND name NOT LIKE 'sqlite_%'; -- Per-index stats (after ANALYZE): SELECT * FROM sqlite_stat1 WHERE tbl = 'messages';

Exercise

Audit the indexes on Pippa's conversations.db (or any SQLite database you have). For each table, list the indexes and ask: is each one currently justified by a known hot query? Are any redundant (e.g., a composite (a,b) index makes a single (a) index redundant)? Write a short note recommending which to keep, drop, or add.

When to Index — and When Not To

Indexes are not free

Code

External links

Exercise

Progress

Comments 0