sql-index-advisor

Offline index advisor for MySQL / MyBatis. Slow query log in, index recommendations and migration SQL out.

Deterministic rules produce every conclusion — reproducible, unit-tested, and no API key required. The LLM is an optional layer that polishes the explanation text; it can never add, remove or re-rank a finding.

$ sia examples/slow.log --schema examples/schema.json

6 suggestions  4 error  2 warn  0 info  · 5 query fingerprints covered

 1 [error] SIA004 Function or expression on an indexed column orders · examples/slow.log:26 · 7.441s · 4,120,933 rows
   evidence SELECT id, user_id, amount FROM orders WHERE DATE(create_time) = '2026-09-17' LIMIT 100…
   rewrite create_time >= '2026-09-17 00:00:00' AND create_time < '2026-09-18 00:00:00'
   DDL     ALTER TABLE `orders` ADD INDEX `idx_orders_create_time` ((DATE(create_time)));

 2 [error] SIA001 Missing index candidate user_address · examples/slow.log:36 · 5.001s · 2,881,004 rows
   evidence SELECT u.id, u.name, u.mobile FROM users u LEFT JOIN user_address a ON a.user_id = u.id…
   DDL     ALTER TABLE `user_address` ADD INDEX `idx_user_address_city_user_id` (`city`, `user_id`);

Review every suggestion before running it; this tool never touches the database.

$ sia examples/slow.log --schema examples/schema.json --emit-sql add-indexes.sql
-- SIA001 Missing index candidate · error · 903,112 rows scanned
ALTER TABLE `order_item` ADD INDEX `idx_order_item_order_id` (`order_id`);
-- SIA004 Function or expression on an indexed column · error · 4,120,933 rows scanned
ALTER TABLE `orders` ADD INDEX `idx_orders_create_time` ((DATE(create_time)));
-- SIA001 Missing index candidate · error · 1,330,921 rows scanned
ALTER TABLE `orders` ADD INDEX `idx_orders_shop_id_create_time` (`shop_id`, `create_time`);
-- SIA001 Missing index candidate · warn · 2 occurrences · 1,842,930 rows scanned
ALTER TABLE `orders` ADD INDEX `idx_orders_user_id_status_create_time` (`user_id`, `status`, `create_time`);

Every line above carries a rule ID, the evidence SQL, and a DDL you can read and verify. That is the whole design: no black box.

中文文档见 README.zh-CN.md。

Why not just ask an LLM?

Because you cannot review what you cannot reproduce.

Put an endpoint behind --llm and it rewrites only the explanation prose. A test asserts the finding set is byte-for-byte identical with and without it.

Related MCP server: MySQL MCP Server Pro

Install

Distributed straight from GitHub — there is no npm package to install.

# once, globally; the command is `sia`
npm i -g github:JingYu-create520/sql-index-advisor

# or run without installing
npx --yes --package github:JingYu-create520/sql-index-advisor sia -- --help

Requires Node.js ≥ 18. Four runtime dependencies, no native builds. The install builds the bundle from source via npm's prepare hook, so a tagged release is all the repository has to ship.

30-second quickstart

# 1. A slow query log, ranked by real cost
sia /var/log/mysql/slow.log

# 2. One statement, before you ship it
sia query "SELECT * FROM orders WHERE user_id=1 ORDER BY create_time DESC LIMIT 20"

# 3. Whole MyBatis project, plus a migration file you can review
sia mapper src/main/resources/mapper --emit-sql migrations.sql

# 4. Machine-readable, for an agent or a script
sia examples/slow.log --format json

Get a schema.json (this is what unlocks precision)

The tool never connects to your database. Dump the schema yourself with one query over information_schema — verified against a live MySQL 8.0.46:

mysql --database=your_db --raw --skip-column-names < examples/schema-dump.sql > schema.json
sia slow.log --schema schema.json

Without it, rules that depend on existing indexes (SIA002, SIA003, SIA005, SIA007) stay silent and the report tells you so. Silence is never reported as "all clear."

See it work end to end

examples/seed-schema.sql builds a deliberately under-indexed database (200k orders, 400k order items) so the advice has something real to bite on:

docker run -d --name sia-mysql -e MYSQL_ROOT_PASSWORD=sia -e MYSQL_DATABASE=demo \
  -p 13307:3306 mysql:8.0
docker exec -i sia-mysql mysql -uroot -psia demo < examples/seed-schema.sql
docker exec -i sia-mysql mysql --raw --skip-column-names -uroot -psia demo \
  < examples/schema-dump.sql > schema.json

sia query "SELECT * FROM orders WHERE user_id=42 AND status='PAID' ORDER BY create_time DESC LIMIT 20" \
    --schema schema.json --emit-sql add-indexes.sql
docker exec -i sia-mysql mysql -uroot -psia demo < add-indexes.sql
docker exec sia-mysql mysql -uroot -psia demo -e \
  "EXPLAIN SELECT * FROM orders WHERE user_id=42 AND status='PAID' ORDER BY create_time DESC LIMIT 20\G"

The last command is the payoff. Before, EXPLAIN falls back to the partial idx_user_pay, estimates 23 rows and reports Using filesort. After the recommended (user_id, status, create_time), it estimates 1 row and the filesort is gone.

Rules

Full reasoning, false-positive boundaries and examples for each: docs/rules.md (中文).

Output formats

Other flags: --min-severity warn · --fail-on error · --top 20 · --mysql-version 5.7 · --prefix-bytes 3072 · --deep-offset 10000 · --rules SIA001,SIA004 · --llm.

Exit codes: 0 nothing at or above --fail-on · 1 findings at or above it · 2 runtime error. Default --fail-on error, so adopting this on day one does not turn your build red.

Use it from an AI coding agent

MCP server — add to claude_desktop_config.json, Qoder's MCP settings, or .cursor/mcp.json. After the global install above, the short form is:

{
  "mcpServers": {
    "sql-index-advisor": {
      "command": "sia",
      "args": ["mcp"]
    }
  }
}

To run it without a global install, use command: "npx" with args: ["--yes", "--package", "github:JingYu-create520/sql-index-advisor", "sia", "--", "mcp"], or from a checkout: command: "node", args: ["dist/mcp.js"].

Four tools: analyze_sql, analyze_slow_log, analyze_mapper, explain_rules. Every tool accepts an inline schema (JSON text or a path), so an agent with no filesystem access still gets schema-accurate advice.

Agent Skill — skills/sql-index-advisor/SKILL.md teaches an agent when to call the CLI, how to read Finding, and the rules it must not break (never execute DDL, always carry the rule ID, always surface skipped).

GitHub Action — annotations on the changed lines:

- uses: JingYu-creates20/sql-index-advisor@v0
  with:
    path: src/main/resources/mapper
    schema: schema.json        # optional but much more precise
    min-severity: warn
    fail-on: "off"             # error  => gate the PR

Full example workflow: examples/github-action/pr-review.yml.

The LLM layer (optional, non-decision-making)

export SIA_LLM_BASE_URL=https://api.openai.com/v1   # any OpenAI-compatible endpoint
export SIA_LLM_API_KEY=sk-...
export SIA_LLM_MODEL=gpt-4o-mini
sia slow.log --llm

Off by default: no key, no network, deterministic text. When on, the endpoint is only asked for operational commentary ("verify the write rate before adding this"), which lands in finding.llmNote. Timeouts and 5xx errors fall back to the built-in template, so a flaky endpoint cannot break a CI gate.

What this tool deliberately does not do

• Never connects to your database. No EXPLAIN, no live statistics. Read-only file analysis, so it can run in a sandboxed CI job. (A --explain mode is planned for v2.)

• Never executes anything. It writes ALTER TABLE text into a file for a human to review. It never emits DROP.

• No auto-indexing. An index is a write-amplification decision with business context; that decision stays with you.

• MySQL only. No PostgreSQL or other dialects.

• Prefers silence to guessing. Known gaps, documented rather than papered over:

  • LIMIT #{offset}, #{size} in a Mapper carries no static value → SIA006 cannot judge it. Feed it a slow log instead.

  • mobile = ? bound to a Java Long → SIA005 cannot see the parameter type.

  • Correlated subqueries are not expanded; SIA006 degrades to a template rather than risk a rewrite that changes the result set.

Supported SQL subset: single SELECT / INSERT / UPDATE / DELETE, ANSI and comma joins, WHERE with =, IN, ranges, BETWEEN, prefix LIKE, IS NULL, GROUP BY, ORDER BY, LIMIT. Anything outside it is skipped with an info note — never a crash, never a fabricated recommendation.

Development

npm ci
npm run typecheck     # tsc --noEmit, strict + noUncheckedIndexedAccess
npm test              # vitest: parsers, 7 rules, engine, reports, MCP (incl. a real stdio handshake)
npm run build         # tsup -> dist/
node dist/cli.js examples/slow.log

206 tests. Beyond hand-written cases, tests/fuzz.test.ts generates ~1,200 statements plus a list of deliberately malformed ones and asserts the properties that must hold for any input: never throw, never index a column that does not exist, never propose an index another already covers, and produce byte-identical output on repeated runs. That suite is what caught the tokenizer reading 1e999 as 1 + a column named e999, and signed literals splitting one query pattern into two fingerprints. Fixtures under tests/fixtures/ are real-shaped MySQL 8.0 logs, including a messy one with administrator commands, multi-line statements and an unterminated tail.

License

MIT — see LICENSE.

More from this author

• spring-review — the same idea for Spring transaction pitfalls, N+1 and ${} injection in MyBatis XML.