index-optimization

intent

optimize database indexes for a single table or collection through end-to-end analysis: locate all relevant queries with code positions, design reusable indexes per database type, and iterate via explain until target index hits. use this when query performance is degrading or you need to consolidate redundant indexes. supports two optimization modes (full-scan vs single-query) and two execution modes (manual confirm vs auto-execute).

inputs

required

• table or collection name (string): exact name of target table or collection; required before any step
• database type and major version (enum): MySQL, TiDB, PostgreSQL, or MongoDB; include version (e.g. MySQL 8, PostgreSQL 14). if unknown, skill will assume MySQL 8 and state assumption explicitly
• explain execution environment (string): dev or sit preferred; prod queries allowed only for read-only explain

optional

• optimization mode (enum, default "full-scan"):
  • full-scan: locate and analyze all queries against table/collection (default)
  • single-query: analyze only one user-specified query and existing indexes
• execution confirmation mode (enum, default "auto"):
  • auto: execute index create/drop without waiting for user confirm (but log "auto-executed" in output)
  • manual: require explicit user confirmation before any index create/drop
• target query (string, required if mode is single-query): the exact query statement to optimize
• query code location (string, optional): format path:line where query appears in codebase
• existing indexes (list, optional): current indexes on table/collection; skill will query database if not provided

external connections

• database connection (required): set env vars per database type:
  • MySQL: DB_HOST, DB_PORT, DB_USER, DB_PASS, DB_NAME
  • PostgreSQL: PGHOST, PGPORT, PGUSER, PGPASSWORD, PGDATABASE
  • MongoDB: MONGODB_URI or MONGO_HOST, MONGO_PORT, MONGO_USER, MONGO_PASS
  • TiDB: same as MySQL
• codebase access (required): local checkout with $CODEX_HOME set; used by collect_table_queries.py
• code search tool (optional): ripgrep (rg) for query location fallback

edge cases to handle

• empty result set: table exists but no queries found; output "no optimization candidates" and stop
• stale statistics: database stats older than 1 week; recommend ANALYZE TABLE before explain
• rate limit: if database rejects repeated explain calls, add 500ms delay between calls
• auth expiry: database token/session expired mid-skill; reconnect or abort with clear error
• network timeout: database unreachable for >30s; retry once then fail with connection string masked
• index name collisions: new index name conflicts with existing index; append _v2 suffix and re-verify

procedure

step 1: confirm mode and collect query inputs

inputs: optimization mode (or default), table name, database type
outputs: mode decision recorded, query list or target query identified

• if mode is full-scan (default):
  • run query location script: python3 $CODEX_HOME/skills/index-optimization-skill/scripts/collect_table_queries.py --table <table_name> --repo-root <repo_root>
  • supplement with manual grep if script misses queries:
    • SQL: rg -n --no-heading -S "<table_name>|query\\.|\\bTable\\(" apps pkg docs
    • MongoDB: rg -n --no-heading -S "<table_name>|Collection\\(|\\.find\\(|\\.aggregate\\(|\\.explain\\(" apps pkg docs
  • preserve all code positions in format path:line
  • convert to clickable markdown: [path:line](/absolute/path/to/file#Lline)
  • output full query list with locations
• if mode is single-query:
  • accept target query statement from user
  • accept optional code location path:line
  • query database for existing indexes: SHOW INDEX FROM <table> (MySQL/TiDB), SELECT indexname, indexdef FROM pg_indexes WHERE tablename = '<table>' (PostgreSQL), db.<collection>.getIndexes() (MongoDB)
  • output target query, location (if provided as clickable link), and current indexes

step 2: normalize and group query patterns

inputs: query list (full-scan) or target query (single-query)
outputs: access pattern groups with examples

• classify each query by access type:
  • point lookups: single row or few rows, equality conditions (=)
  • range/pagination: comparison (>, <, >=, <=, BETWEEN, IN) and sort (ORDER BY ... LIMIT)
  • joins: JOIN keys and directionality
  • write paths: UPDATE or DELETE conditions
  • optional filters: low-selectivity columns (status, is_del, type, etc.)
• in single-query mode, analyze only the target query; do not expand to other queries
• output pattern summary with query examples and count

step 3: design reusable indexes per database type

inputs: access patterns, database type, existing indexes
outputs: candidate index list with DDL, coverage, and explain targets

• consult references/index-rules.md for database-specific rules
• prioritize: fewer indexes covering highest-frequency shared patterns
• avoid: overlapping or redundant index prefixes
• for each candidate index, output:
  • DDL statement (SQL or MongoDB createIndex syntax)
  • which access patterns it covers (list pattern names and count of queries)
  • code locations using it (clickable links: [path:line](/absolute/path/to/file#Lline))
  • expected explain targets: key/index name, type (ref, range, ALL, COLLSCAN, etc.), estimated rows, Extra or execution node
• if multiple indexes are near-equal in value, pick one and mark others as "alternative"

step 4: provide evidence and execute confirmation logic

inputs: index design, execution confirmation mode
outputs: confirmation decision (auto or manual) logged, rationale recorded, DDL ready

• output before executing any create/drop:
  1. change type: new index, delete index, replace (drop old + create new)
  2. rationale: which queries, code locations, explain evidence (if available pre-verification), expected benefit (latency %, scans reduced by %)
  3. risk assessment: write overhead (% slower inserts/updates), locking impact (how long table is locked), rollback plan (exact DDL to undo)
  4. DDL statement: exact SQL or MongoDB command to execute
• apply confirmation logic:
  • if mode is manual: output "awaiting user confirmation" and pause. only proceed to step 5 after explicit user approval
  • if mode is auto: output "auto-executing per auto mode" and proceed immediately to step 5
• special gate for deletes: before dropping any existing index, verify all three conditions:
  1. poor performance evidence: explain shows index never hits, or hits but scans much more than candidate index
  2. replacement confirmed: document which new or existing index replaces it
  3. no regression: run explain before-and-after on core queries, confirm no worse performance (same index hit, same or fewer scans, no slow plan changes)
  • if any condition fails, mark index as "candidate for deletion" only; do not execute DROP

step 5: execute and explain verify

inputs: DDL statement, target queries, database connection
outputs: explain results before and after, index hit status, rollback statement

• create SQL output file: docs/sql/<YYYYMMDD>_<table>_index_optimization.sql with all DDL
• for MongoDB: output createIndex and corresponding dropIndex (rollback)
• run explain on each representative query (or target query in single-query mode) before index change:
  • MySQL/TiDB: EXPLAIN SELECT ... capture possible_keys, key, type, rows, Extra
  • PostgreSQL: EXPLAIN <query> capture Seq Scan, Index Scan, node type and rows
  • MongoDB: db.<collection>.find(...).explain("executionStats") capture executionStages.stage and executionStats.nReturned
• execute index create/drop
• run explain on same queries after change
• record output with explicit index name (e.g. "idx_user_email_created" or "NONE" if missed)
• compare: confirm expected index is hit, scan count stable or reduced
• if deleting an index, wait for replacement index to hit before dropping original
• output all explain before/after inline with annotations

step 6: iterate or stop

inputs: explain results
outputs: final index state or iteration record

• if explain shows target index hit and performance acceptable: proceed to "outcome signal"
• if explain misses target index:
  • diagnose: wrong leading column order, low selectivity, sort mismatch, implicit type coercion, stale stats, optimizer choice
  • adjust design: reorder columns, add/remove suffix columns, split wide index, remove redundant index
  • re-run explain
  • repeat until hit or 3 redesigns exhausted
• if 3 redesigns fail: stop, output root cause analysis and best fallback (e.g. query rewrite, partitioning, caching)

decision points

mode selection:

• if user specifies single-query mode with target query: go to step 1 (single-query branch), skip full-scan; must query existing indexes first
• if user specifies full-scan or provides no mode: go to step 1 (full-scan branch), run query location script

confirmation gate:

• if execution mode is auto: skip user confirm, log "auto-executing" with timestamp, proceed to step 5
• if execution mode is manual: output pending confirm status, wait for explicit user approval before step 5; if user rejects, stop and output "cancelled per user"