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