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');

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:

Example:

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.

Example Interpretation

Let's look at this example:

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.

koriym / sql-quality

Maintainers

Details