# Index Design

> B-tree indexes, composite indexes, covering indexes, UNIQUE indexes, and index trade-offs.

- Skill: `sethdford/index-design` (Agent Skill)
- Install (CLI): `npx skillmds@latest add sethdford/index-design`
- Raw SKILL.md: https://api.skillmd.com/api/skills/sethdford/index-design/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: sethdford (https://skillmd.com/u/sethdford)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/sethdford/index-design

---


# Index Design

Strategic indexing for query performance without write overhead.

## Context

You are deciding which indexes to create. Index every query path, but don't overdo it.

## Domain Context

- **B-tree**: Default; works for most queries
- **Composite**: Multiple columns; order matters for range queries
- **Covering**: Includes all columns query needs; avoids table lookup
- **Unique**: Enforce uniqueness; sometimes performance benefit
- **Tradeoff**: Faster reads, slower writes, more storage

## Instructions

1. **Index WHERE Clauses**: Columns filtered
2. **Index JOIN Columns**: Foreign keys and join conditions
3. **Index ORDER BY**: Sorted columns
4. **Composite Order**: Equality first, then range, then sorts
5. **Covering Indexes**: Include SELECT columns; avoid table lookup
6. **Monitor**: Check that indexes are actually used
7. **Remove Unused**: Unused indexes waste storage and slow writes

## Anti-Patterns

- Too many indexes; slows writes, confuses optimizer
- Wrong index order in composite; doesn't help range queries
- Indexes on low-cardinality columns; sequential scan is faster
- Not considering write cost; read optimization kills write performance
- Indexes on every column; shotgun approach

## Further Reading

- PostgreSQL indexes documentation
- MySQL indexes documentation
- SQL Performance Explained (Markus Winand)