---

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

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

Re-analyze and record SQL fix impact.

Step 2: Create Indexes (one by one)

For each suggested index:

1. Create index

   CopyCREATE INDEX idx_name ON table(columns);
   

2. Re-analyze

3. Evaluate impact

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

     CopyDROP INDEX idx_name ON table;
     

     Record as "ineffective, rolled back"

Step 3: Generate Report

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

Copy{
  "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:

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

Output Summary

CopySQL 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