A powerful MySQL query analyzer that helps detect potential performance issues in SQL files and provides AI-powered optimization recommendations.
- 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
- PHP 8.1+
- MySQL 5.7+ or MariaDB 10.2+
- PDO MySQL extension
composer require koriym/sql-quality<?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');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| 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 |
For Claude Code users, automated SQL optimization skills are available:
From Marketplace:
# Add marketplace
/plugin marketplace add koriym/Koriym.SqlQuality
# Install plugin (includes all 3 skills)
/plugin install sql-quality@sql-qualityFor 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).
# 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/sqlThese 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.
Example:
The analyzer generates two types of analysis reports in the specified output directory (e.g., build/sql-quality).
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 |
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.) |
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.
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.
The summary report also includes overall project statistics:
- Total SQL queries analyzed
- Average query cost
- Standard deviation of costs
SQL query analysis results support multilingual output in both ExplainAnalyzer and AIQueryAdvisor.
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.