sql-quality-fix

name	sql-quality-fix
description	Automatically fix SQL performance issues with step-by-step measurement. Rewrites problematic SQL patterns (functions on columns, implicit conversions), creates indexes, measures their impact, and rolls back ineffective indexes. Reports improvements at each step with cost reduction percentages.

SQL Quality Fix

Automatically fix SQL performance issues with step-by-step measurement.

Arguments

• $ARGUMENTS: SQL directory and params file
  • Example: "tests/sql tests/params/sql_params.php"
  • With flag: "tests/sql tests/params/sql_params.php --no-index"

Options

• --no-index: Skip index creation, only suggest DDL

Steps

Step 0: Initial Analysis

php bin/sql-quality analyze \
  --sql-dir="$(echo $ARGUMENTS | cut -d' ' -f1)" \
  --params="$(echo $ARGUMENTS | cut -d' ' -f2)" \
  --format=json

Record as baseline.

Step 1: Fix SQL Files

Apply SQL fixes:

Issue	Fix
FullTableScan	Add WHERE with indexed columns
FunctionInvalidatesIndex	Rewrite: YEAR(col)=2024 → col >= '2024-01-01'
IneffectiveLikePattern	Use prefix match if possible
IneffectiveJoin	Reorder JOINs, use explicit syntax

Re-analyze and record SQL fix impact.

Step 2: Create Indexes (one by one)

For each suggested index:

1. Create index

   CREATE INDEX idx_name ON table(columns);
   

2. Re-analyze

3. Evaluate impact

   • Cost improved ≥ 5% → Keep index
   • Cost not improved → Rollback

     DROP INDEX idx_name ON table;
     

     Record as "ineffective, rolled back"

Step 3: Generate Report

Save to build/sql-quality/fix-result.json:

{
  "executed_at": "2024-01-15T10:30:00",
  "steps": [
    {
      "step": "initial",
      "total_cost": 650.00
    },
    {
      "step": "sql_fix",
      "total_cost": 450.00,
      "improvement": "-30.8%",
      "changes": [
        {"file": "1_full_table_scan.sql", "change": "Added WHERE user_id = :user_id"}
      ]
    },
    {
      "step": "index",
      "total_cost": 57.30,
      "improvement": "-87.3%",
      "indexes_created": [
        {"ddl": "CREATE INDEX idx_posts_user_id ON posts(user_id)", "impact": "-60%"}
      ],
      "indexes_rolled_back": [
        {"ddl": "CREATE INDEX idx_posts_title ON posts(title)", "reason": "no improvement"}
      ]
    }
  ],
  "final": {
    "total_cost": 57.30,
    "total_improvement": "-91.2%"
  },
  "manual_review_needed": []
}

Generate markdown:

php bin/sql-quality report --input=build/sql-quality/fix-result.json

Output Summary

SQL Quality Fix: Complete

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Step-by-Step Improvement
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
| Step      | Cost   | Change |
|-----------|--------|--------|
| Initial   | 650.00 | -      |
| SQL Fix   | 450.00 | -30.8% |
| Index     | 57.30  | -87.3% |
| **Final** | **57.30** | **-91.2%** |

SQL Changes:
  ✓ 1_full_table_scan.sql: Added WHERE clause

Indexes Created:
  ✓ idx_posts_user_id (-60% cost)

Indexes Rolled Back (ineffective):
  ✗ idx_posts_title (no improvement)

Manual Review:
  (none)

Report: build/sql-quality/fix-report.md