How do I install the Index Optimizer skill?

Run npx skillmds@latest add huifer/index-optimizer in your terminal (requires Node.js), paste this page's agent-chat prompt into Claude, Cursor, or any MCP-connected agent, or download the SKILL.md file and copy it into your agent's skills directory.

Is Index Optimizer safe to use?

This skill has not completed SkillMD's automated safety review yet. SkillMD never runs a skill's scripts for you; review the SKILL.md before installing.

Which AI agents work with Index Optimizer?

This skill is tagged as working with Claude Code, Claude.ai, OpenAI Codex. SKILL.md is an open format, so most agents that read a skills directory can load it too.

Is Index Optimizer free to use?

Yes. Installing skills from SkillMD is free, and the skill stays under its author's original license.

Who published Index Optimizer?

huifer (@huifer) published this skill. Their other Agent Skills are listed on their SkillMD profile.

Index Optimizer by huifer — Agent Skill

Name: Index Optimizer
Author: huifer

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.

huifer f0ca8d8 11 files · 21.9 KB Updated Sep 17, 2026

File contents

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.

---name: index-optimizerdescription: 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.---# 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 IndexesCheck sequential scans and slow queries.See `references/composite-index.md` for multi-column patterns.### Step 2: Check Redundant IndexesFind indexes that are subsets of other indexes.See `references/redundant-index.md` for detection.### Step 3: Detect Unused IndexesMonitor index usage statistics.See `references/unused-index.md` for detection and removal.### Step 4: Optimize Index DesignApply 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. ImplementationApply fixes from referenced files.```

Frequently asked questions