# Index Optimizer

> Database indexing optimization expert. Identifies missing, redundant, and unused indexes, and provides optimization recommendations. Trigger when user mentions index optimization, query performance, or database tuning.

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

---


# Database Index Optimization Expert

## Index Types

| Type | Use Case | Trade-off |
|------|----------|-----------|
| Single Column | Simple equality/range | Low overhead |
| Composite | Multi-column queries | Column order matters |
| Partial | Subset of rows | Smaller, faster |
| Covering | Index-only scans | Larger, eliminates lookups |

## Common Anti-Patterns

| Anti-Pattern | Problem | Reference |
|--------------|---------|-----------|
| Missing composite index | Multi-column queries slow | `references/composite-index.md` |
| Missing covering index | Table lookups required | `references/covering-index.md` |
| Missing partial index | Indexing all rows | `references/partial-index.md` |
| Unused indexes | Write overhead | `references/unused-index.md` |
| Redundant indexes | Duplicate coverage | `references/redundant-index.md` |
| Index skip scan | Wrong column order | `references/index-skip-scan.md` |

## Audit Workflow

### Step 1: Identify Missing Indexes

Check sequential scans and slow queries.
See `references/composite-index.md` for multi-column patterns.

### Step 2: Check Redundant Indexes

Find indexes that are subsets of other indexes.
See `references/redundant-index.md` for detection.

### Step 3: Detect Unused Indexes

Monitor index usage statistics.
See `references/unused-index.md` for detection and removal.

### Step 4: Optimize Index Design

Apply appropriate index type based on query patterns:
- Multi-column WHERE → `references/composite-index.md`
- Subset queries → `references/partial-index.md`
- SELECT-heavy → `references/covering-index.md`

## Benchmark Reference

| Optimization | Before | After | Improvement |
|--------------|--------|-------|-------------|
| Composite index | 500ms | 5ms | 99% |
| Partial index | 50MB | 5MB | 90% size reduction |
| Covering index | 50ms | 10ms | 80% faster |
| Remove unused | 100ms write | 70ms write | 30% faster |
| Remove redundant | 200MB | 100MB | 50% reduction |

## Output Format

```
## Index Optimization Report

### 1. Current State
- Total indexes: X
- Missing indexes: X
- Redundant indexes: X
- Unused indexes: X

### 2. Anti-Patterns Detected
| Table | Anti-Pattern | Reference | Fix |
|-------|--------------|-----------|-----|
| ... | ... | ... | ... |

### 3. Recommendations (by priority)
1. [P0] Fix: <anti-pattern> → Expected improvement: XX%
   Reference: references/<file>.md

### 4. Implementation
Apply fixes from referenced files.
```