sql-quality

Koriym.SqlQuality

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 --dsn="mysql:host=localhost;dbname=mydb" --user=root --password=secret
sql-quality analyze --sql-dir=sql/ --params=params.php --lang=ja

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
--lang=LANG	Language for messages: en or ja	en

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, 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.