@multisystemsuite/index-advisor

Recommend database indexes from query workloads: missing, compound, and covering indexes; detect unused and redundant indexes; generate CREATE INDEX migration SQL.

Version: 1.0.1 · License: MIT

npm readme: @multisystemsuite/index-advisor on npm

Installation

pnpm add @multisystemsuite/index-advisor

Quick start

import { IndexAdvisor } from '@multisystemsuite/index-advisor';

const advisor = new IndexAdvisor({ dialect: 'mysql' });

advisor.recordQuery({
  query: 'SELECT * FROM users WHERE email = ?',
  executionTime: 120,
});

const recommendations = advisor.analyzeMissingIndexes();
console.log(recommendations[0].migrationSql);

Example output:

CREATE INDEX idx_users_email
ON users(email);

Configuration

| Option | Default | Description | |--------|---------|-------------| | dialect | mysql | mysql or postgresql (SQL syntax) |

API reference

recordQuery(input: QueryTrackInput)

Add a query to the workload log for analysis.

analyzeMissingIndexes(): IndexRecommendation[]

Returns recommendations sorted by table usage.

IndexRecommendation fields:

| Field | Description | |-------|-------------| | table | Table name | | columns | Recommended column(s) | | type | single | compound | covering | | reason | Human-readable rationale | | migrationSql | Ready-to-run DDL | | priority | low | medium | high | critical |

findUnusedIndexes(existing, minScans?)

Pass catalog indexes with scans count; returns indexes below threshold (default 10).

advisor.findUnusedIndexes([
  { name: 'idx_legacy', table: 'users', columns: ['legacy_col'], scans: 0 },
]);

findRedundantIndexes(existing)

Detects prefix-redundant indexes (e.g. (email) redundant when (email, status) exists).

generateMigration(recommendation)

Returns recommendation.migrationSql.

Workflow

flowchart LR
  A[Record queries] --> B[analyzeMissingIndexes]
  B --> C[Review migrationSql]
  C --> D[Apply via db-migrator]

1. Record production-like queries over time.
2. Run analyzeMissingIndexes() weekly.
3. Copy migrationSql into @multisystemsuite/db-migrator migrations.

Related packages

See the @multisystemsuite org on npm.

npm

• Package: @multisystemsuite/index-advisor
• Install: npm install @multisystemsuite/index-advisor