README

Japanese

A powerful MySQL query analyzer that helps detect potential performance issues in SQL files and provides AI-powered optimization recommendations.

Features

Detects common performance issues (full table scans, inefficient JOINs, etc.)
Provides AI-powered optimization recommendations
Supports multiple output languages
Generates detailed analysis reports in Markdown format

Requirements

PHP 8.1+
MySQL 5.7+ or MariaDB 10.2+
PDO MySQL extension

Installation

composer require koriym/sql-quality

Usage

<?php

namespace Koriym\SqlQuality;

use PDO;
use function dirname;

require dirname(__DIR__) . '/vendor/autoload.php';

$pdo = new PDO('mysql:host=127.0.0.1;dbname=test', 'root', '', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
]);

$sqlParams = require 'path/to/sql_params.php';

//return [
//    '1_full_table_scan.sql' => ['min_views' => 1000],
//    '2_filesort.sql' => ['status' => 'published', 'limit' => 10]
//];

$analyzer = new SqlFileAnalyzer(
    $pdo,
    new ExplainAnalyzer(),
    'path/to/sql_dir',
    new AIQueryAdvisor('以上の分析を日本語で記述してください。')
);

// Output to build/sql-quality
$analyzer->analyzeSqlDirectory($sqlParams, __DIR__ . '/build/sql-quality');

CLI Usage

sql-quality analyze --sql-dir=sql/ --params=params.php --format=json
sql-quality analyze --sql-dir=sql/ --params=params.php --format=markdown --output=build/sql-quality
sql-quality analyze --sql-dir=sql/ --params=params.php --fail-on=critical
sql-quality analyze --sql-dir=sql/ --params=params.php --dsn="mysql:host=localhost;dbname=mydb" --user=root --password=secret
sql-quality analyze --sql-dir=sql/ --params=params.php --lang=ja
sql-quality explain --sql-file=sql/1_full_table_scan.sql --params='{"min_views":1000}'

Options

Option	Description	Default
`--sql-dir=DIR`	Directory containing SQL files (required)
`--params=FILE`	PHP file returning SQL parameters array (required)
`--dsn=DSN`	Database DSN	`mysql:host=127.0.0.1;dbname=test`
`--user=USER`	Database user	`root`
`--password=PASS`	Database password	(empty)
`--format=FORMAT`	Output format: `json` or `markdown`	`json`
`--output=DIR`	Output directory for markdown reports (required with `--format=markdown`)
`--fail-on=LEVEL`	Exit 1 when an issue of `critical`, `warning` or `info` level or above is found	none
`--lang=LANG`	Language for messages: `en` or `ja`	`en`

`explain`

Analyzes one SQL file and prints a single structured JSON report, instead of scanning a whole directory. --dsn, --user, --password, --fail-on and --lang behave the same as for analyze.

Option	Description	Default
`--sql-file=FILE`	Single SQL file to explain (required)
`--params=JSON\|FILE`	Inline JSON object, or a PHP file in the same format as `analyze`'s `--params`	`{}`

Exit codes

Code	Meaning
`0`	No issue reached the `--fail-on` level
`1`	An issue reached the `--fail-on` level
`2`	Usage, database connection, params file, or unknown `--format` error; for `explain`, also a query that cannot be explained (e.g. DDL) or that MySQL rejects (e.g. a missing table)

For analyze, a file that cannot be analyzed does not change the exit code — it is listed with its reason under skipped in the JSON output. For explain, the same kind of failure has no file to skip into, so it exits with code 2 and prints the reason to stderr instead.

JSON Schema

Both commands' JSON output conforms to a schema in schema/: analyze-report.schema.json for analyze --format=json, explain-report.schema.json for explain. An agent can validate the CLI output against either schema before acting on it.

Claude Code Skills

For Claude Code users, automated SQL optimization skills are available:

Installation

From Marketplace:

# Add marketplace
/plugin marketplace add koriym/Koriym.SqlQuality

# Install plugin (includes all 3 skills)
/plugin install sql-quality@sql-quality

For Project Developers: When you trust this project folder, Claude Code will automatically prompt you to add the marketplace and enable the plugins (configured in .claude/settings.json).

Usage

# Analyze SQL files (CI-friendly)
/sql-quality-check tests/sql tests/params/sql_params.php

# Auto-fix issues with step-by-step measurement
/sql-quality-fix tests/sql tests/params/sql_params.php

# Generate parameter bindings from SQL files
/sql-params-generate tests/sql

Features

These AI-powered skills:

Detect performance issues (FullTableScan, IneffectiveJoin, CartesianProduct, EstimateDivergence, etc.)
Rewrite problematic SQL patterns (functions on columns, implicit conversions)
Create indexes and measure their impact in real-time
Roll back ineffective indexes automatically
Generate detailed improvement reports with cost reductions

See skills/*/SKILL.md for detailed documentation.

Analysis Reports

Example:

SQL Analysis Summary

The analyzer generates two types of analysis reports in the specified output directory (e.g., build/sql-quality).

1. Query Analysis List

Shows the overall analysis of each SQL query:

Column	Description
SQL File	Name of the SQL file
Cost	Estimated query cost
Level	Performance level based on statistical analysis (μ = mean, σ = standard deviation)
Issues	Detected performance issues
Report	Link to detailed analysis

Example:

SQL File	Cost	Exec Time (ms)	Level	Issues	Report
1_full_table_scan.sql	497.95	5.92	Medium (μ ± σ)	FullTableScan	Details

2. Queries with Optimizer Impact

The MySQL Query Optimizer is a crucial component that automatically optimizes query execution plans. Even when SQL and index design are not optimal, the optimizer attempts to improve performance at runtime.

Column	Description
SQL File	Name of the SQL file
Base Access	Access method, row count, and scan percentage with optimizer disabled
Optimized Access	Access method, row count, and scan percentage with optimizer enabled
Cost Impact	Cost reduction percentage by optimizer (negative values indicate improvement)
Base Issues	Issues detected when optimizer is disabled
Plan Changes	Detailed execution plan changes (filtering ratio, cost changes, etc.)

Example Interpretation

Let's look at this example:

SQL File	Base Access	Optimized Access	Cost Impact	Base Issues	Plan Changes
11_nested_loop.sql	ALL, 4897 rows, 100.0%	ALL, 1000 rows, 10.0% → ref, using idx_posts_user_id, 4 rows, 100.0%	-44.9%	FullTableScan	-

In this example, without the optimizer, the query performs a full table scan processing 4,897 rows. With the optimizer enabled, it uses an index to access only 4 rows. The cost reduction of -44.9% indicates a significant improvement through optimizer intervention.

Understanding Optimizer Impact

While the optimizer improves performance, relying on it may mask potential underlying issues. Additionally, there are risks of unstable performance as data volume grows or statistics change. This feature aims to detect such issues early and guide appropriate solutions by comparing execution plans and performance with and without the optimizer.

Project Statistics

The summary report also includes overall project statistics:

Total SQL queries analyzed
Average query cost
Standard deviation of costs

Multilingual Support

SQL query analysis results support multilingual output in both ExplainAnalyzer and AIQueryAdvisor.

Language Customization in ExplainAnalyzer

While English is the default language, you can customize error messages in ExplainAnalyzer constructor for other languages:

// Japanese error messages
$analyzer = new ExplainAnalyzer([
    'FullTableScan' => 'フルテーブルスキャンが検出されました。',
    'IneffectiveJoin' => '非効率的な結合が検出されました。',
    'FunctionInvalidatesIndex' => '関数の使用によりインデックスが無効化されています。',
    // ... other messages
]);

// Combined with AI Advisor for complete Japanese output
$analyzer = new SqlFileAnalyzer(
    $pdo,
    $analyzer,
    $sqlDirectory,
    new AIQueryAdvisor('以上の分析を日本語で記述してください。')
);

This allows you to generate the entire analysis report in your preferred language. Both error messages and AI analysis results will be output in the specified language.

Writing a Detector

A detector is a class in src/Detector/ implementing DetectorInterface. detect() receives a QueryContext and returns a list of Finding, one per table or plan node it matched:

final class FullTableScanDetector implements DetectorInterface
{
    /** @return list<Finding> */
    public function detect(QueryContext $context): array
    {
        $findings = [];
        foreach ($context->tables() as $table) {
            if ($table['access_type'] === 'ALL') {
                $findings[] = new Finding(array_intersect_key($table, array_flip(['table_name', 'rows_examined_per_scan', 'possible_keys', 'key'])));
            }
        }

        return $findings;
    }
}

QueryContext holds the interpolated sql, explain (EXPLAIN FORMAT=JSON), explainAnalyze, warnings (SHOW WARNINGS), schema (information_schema per table) and optimizerTrace (an excerpt of information_schema.OPTIMIZER_TRACE for the default-optimizer EXPLAIN, null when the trace is unavailable: not captured, unreadable or truncated). tables(), tableAccesses(), nestedLoops(), warningsWithCode(), indexColumns(), aliases(), schemaFor(), columnType(), primaryKeyColumns() and optimizerTraceFor() read them.

Finding::$evidence holds the values the detector based its decision on: use the EXPLAIN key names for values taken from the plan, and include table_name when the finding is about one table. It is reported as evidence on the issue. severity, confidence and suggestion are optional; when set they replace the defaults for the warning type.

Test a detector against the recorded plans in tests/fixtures/: Fixture::load('1_full_table_scan.sql') returns the QueryContext for tests/sql/1_full_table_scan.sql, and tests/fixtures/expected.php lists the types every fixture must produce. To register a detector, add it to the ExplainAnalyzer constructor, its type to WarningType and WarningMessages in src/Types.php, its default message to ExplainAnalyzer::DEFAULT_MESSAGES, and its page to docs/issues/.

koriym / sql-quality

Package info

Security

Statistics

README

Features

Requirements

Installation

Usage

CLI Usage

Options

`explain`

Exit codes

JSON Schema

Claude Code Skills

Installation

Usage

Features

Analysis Reports

1. Query Analysis List

2. Queries with Optimizer Impact

Example Interpretation

Understanding Optimizer Impact

Project Statistics

Multilingual Support

Language Customization in ExplainAnalyzer

Writing a Detector