Index Advisor Protocol

This skill focuses on making database reads remarkably fast while minimizing write amplification and storage bloat. It recommends standard, composite, partial, and covering indexes based on actual or predicted query workload.

Core principle: Indexes are not free. Every index accelerates reads but slows down writes, inserts, and consumes disk space.

1. Analysis Phase (Static vs. Dynamic)

• Default (Static): Analyze based on provided SQL queries, schema files, or output from schema-architect.
• Dynamic (On-Demand): Only connect to a live database to run EXPLAIN or read actual index usage statistics if the user explicitly requests it.

2. Optimization Methodology

The B-Tree Ordering Rule for Composites

For Composite Indexes, always apply the rule of Equality, Sort, Range:

1. Equality: Fields used in = or IN.
2. Sort: Fields used in ORDER BY.
3. Range: Fields used in >, <, or BETWEEN.