Database Indexes — Types & Trade-offs
debt(d5/e3/b7/t5)
Closest to 'specialist tool catches' (d5). The term's detection_hints list mysql-slow-query-log, pt-query-digest, and percona-advisor — all specialist tools that identify missing or problematic indexes via EXPLAIN analysis and slow query monitoring. Standard linters won't catch index issues; you need database-specific profiling tools.
Closest to 'simple parameterised fix' (e3). The quick_fix states adding an index on columns used in WHERE/JOIN/ORDER BY clauses. Adding or dropping a single index is typically a one-line DDL statement (e1), but proper index strategy often requires reviewing multiple queries and may involve composite indexes or removing unused ones — touching multiple migration files or affecting several queries, pushing toward e3.
Closest to 'strong gravitational pull' (b7). Index design is a load-bearing architectural decision with wide reach — applies_to shows web, cli, and queue-worker contexts. Poor indexing strategy pervades the entire application's database interactions. Every new query pattern must consider existing indexes, every schema change must account for index maintenance costs. The decay flavor applies: wrong index choices slowly poison performance as data grows.
Closest to 'notable trap' (t5). The misconception explicitly states 'More indexes always improve query performance' — a documented gotcha that most developers eventually learn but initially get wrong. The hidden write overhead and query planner confusion from over-indexing contradicts the intuitive assumption that indexes are purely beneficial. This is a well-known trap but not catastrophic since the failure mode (slow writes, suboptimal plans) is observable.
Also Known As
TL;DR
Explanation
B-Tree indexes (default in MySQL/PostgreSQL): ordered structure supporting equality, range queries, ORDER BY, and prefix matching (LIKE 'foo%'). Hash indexes: O(1) equality lookups, no range support — PostgreSQL supports them; MySQL memory tables only. Full-text indexes: tokenise text for keyword search (MATCH AGAINST in MySQL, tsvector/GIN in PostgreSQL) — far better than LIKE '%keyword%'. Partial indexes (PostgreSQL): CREATE INDEX ON orders(created_at) WHERE status = 'pending' — indexes only the subset, dramatically reducing size and improving selectivity for filtered queries. Composite indexes: column order matters — put equality conditions first, range conditions last, match GROUP BY/ORDER BY columns. Every index slows INSERT/UPDATE/DELETE — add indexes for proven slow queries, not preemptively.
Diagram
flowchart TD
subgraph B-Tree Index - Default
BT[Ordered tree structure<br/>range queries O of log n<br/>equality O of log n]
end
subgraph Hash Index
HI[Hash map structure<br/>equality only O of 1<br/>no range queries]
end
subgraph GIN Index - Full Text
GIN2[Inverted index<br/>array contains<br/>JSONB keys<br/>full-text search]
end
subgraph Partial Index
PI[Index with WHERE clause<br/>WHERE status=active<br/>smaller faster index]
end
QUERY[Query type] -->|"=, >, <, BETWEEN"| BT
QUERY -->|"= only"| HI
QUERY -->|"@>, ??, tsvector"| GIN2
QUERY -->|"common filter"| PI
style BT fill:#238636,color:#fff
style PI fill:#238636,color:#fff
style GIN2 fill:#6e40c9,color:#fff
Common Misconception
Why It Matters
Common Mistakes
- Adding indexes after performance problems appear instead of designing them with the query patterns.
- Creating single-column indexes when composite indexes would serve multiple queries more efficiently.
- Indexing low-cardinality columns (boolean, status with 3 values) — the optimiser may ignore them anyway.
- Not removing unused indexes — every index adds overhead to INSERT, UPDATE, and DELETE operations.
Code Examples
-- Missing index on foreign key — full table scan on every JOIN:
SELECT o.id, u.email FROM orders o JOIN users u ON o.user_id = u.id;
-- No index on orders.user_id — scans entire orders table per user
-- Add index:
CREATE INDEX idx_orders_user_id ON orders (user_id);
-- EXPLAIN now shows: Index Scan using idx_orders_user_id-- B-Tree (default) — equality, range, ORDER BY
CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_orders_created ON orders(created_at DESC);
-- Composite — equality columns first, range/sort last
CREATE INDEX idx_orders_user_status ON orders(user_id, status, created_at);
-- Partial (PostgreSQL) — only index the interesting subset
CREATE INDEX idx_orders_unpaid ON orders(created_at)
WHERE status IN ('pending', 'processing');
-- Covering — all query columns in the index, no heap fetch
CREATE INDEX idx_orders_cover ON orders(user_id) INCLUDE (total, status);
-- Full-text (PostgreSQL)
CREATE INDEX idx_products_fts ON products USING GIN(to_tsvector('english', name || ' ' || description));
-- Verify index is used
EXPLAIN (ANALYZE) SELECT total FROM orders WHERE user_id = 42;