@multisystemsuite/query-analyzer

Analyze SQL performance: slow queries, missing indexes, duplicates, full table scans, expensive joins, explain plans, and cost estimates.

Version: 1.0.1 · License: MIT

npm: @multisystemsuite/query-analyzer

Installation

pnpm add @multisystemsuite/query-analyzer
npm install @multisystemsuite/query-analyzer

Quick start

import { QueryAnalyzer } from '@multisystemsuite/query-analyzer';

const analyzer = new QueryAnalyzer({ threshold: 1000 });

const result = analyzer.track({
  query: 'SELECT * FROM users WHERE email = ?',
  executionTime: 2500,
});

console.log(result);

Example output:

{
  "severity": "high",
  "score": 25,
  "issue": "Missing Index",
  "recommendation": "Create index on email",
  "fingerprint": "a1b2c3d4e5f6g7h8",
  "duplicateCount": 1,
  "estimatedCost": 2500
}

Configuration

| Option | Default | Description | |--------|---------|-------------| | threshold | 1000 | Slow query threshold (ms) | | maxHistory | 10000 | Max tracked queries in memory | | duplicateThreshold | 3 | Min repeats to flag duplicate queries |

API reference

track(input: QueryTrackInput): QueryAnalysisResult

Records a query execution and returns analysis.

QueryTrackInput fields:

| Field | Required | Description | |-------|----------|-------------| | query | Yes | SQL string | | executionTime | Yes | Duration in ms | | rowsExamined | No | Rows scanned (improves index detection) | | rowsReturned | No | Rows returned | | database | No | Dialect hint | | connectionId | No | Connection identifier |

analyze(input, fingerprint?, duplicateCount?)

Analyze without storing in history.

parseExplainPlan(plan)

Parse MySQL-style explain rows:

analyzer.parseExplainPlan({ type: 'ALL', table: 'users', rows: 100000 });

getSlowQueries(limit?)

Return queries above threshold, sorted by duration.

getDuplicateQueries()

Return fingerprints seen ≥ duplicateThreshold times.

getHistory() / clear()

Access or reset in-memory history.

Detected issues

| Issue | Typical recommendation | |-------|------------------------| | Slow Query | Optimize or add indexes | | Missing Index | Create index on <columns> | | Duplicate Query | Cache or batch identical queries | | SELECT * | Select only required columns | | Full Table Scan | Add WHERE or index | | Expensive Join | Review join order and indexes | | High Query Cost | Reduce scanned rows |

Integration example

// Wrap your database driver
async function executeQuery(sql: string, params: unknown[]) {
  const start = Date.now();
  const result = await db.query(sql, params);
  analyzer.track({
    query: sql,
    executionTime: Date.now() - start,
    rowsReturned: result.rowCount,
  });
  return result;
}

Related packages (npm)

• @multisystemsuite/index-advisor — index suggestions
• @multisystemsuite/core — unified toolkit
• All @multisystemsuite packages

npm

• Package: @multisystemsuite/query-analyzer
• Install: npm install @multisystemsuite/query-analyzer