Why a layered SQL Skill workflow matters
- Typical SQL work includes: identifying the dialect and schema, drafting SQL, inspecting execution plans, and preparing migration or index changes when necessary.
- Dividing work into Skill types (query/report, performance, modeling) reduces the risk of running unverified statements in production and enables repeatable checks.
- The recommended pattern is: produce SQL -> validate with EXPLAIN and test runs -> prepare a reversible DDL/migration if needed.
Picking the right Skill type (conceptual guidance)
- Queries & Reports (when schema is stable and you need metrics)
- Good for: complex joins, aggregations, window functions, and clear reporting grain. - Expected output: executable SELECTs, sample result rows, and explanation of business logic.
- Slow Queries & Index Tuning (when you have a measurable performance problem)
- Good for: examining EXPLAIN, slow-query logs, and runtime baselines. - Expected output: plan analysis, suggested rewrites, candidate indexes with trade-offs.
- Schema & Migration (for new models or structural changes)
- Good for: ER diagrams, DDL with constraints and indexing, and migration scripts with rollback. - Expected output: DDL, migration steps, backfill strategies, and rollback commands.
Note: Always verify outputs in a test environment or with a restricted, read-only connection before applying changes to production.
Worked example: slow report aggregation
Scenario: A region-level monthly report of paying users and total spend becomes slow during peak traffic. Goal: find the bottleneck in a test environment, validate a rewrite or an index solution, and prepare a reversible index migration plan.
Minimum supplied inputs (what you would give to a Skill/agent)
- Engine: MySQL 8.x (state engine and version explicitly).
- Tables: users(id PK, created_at DATETIME, region VARCHAR), orders(id PK, user_id FK, amount DECIMAL, status, created_at).
- Existing indexes: users PK(id); orders PK(id), idx_orders_user_created (user_id, created_at).
- Baseline SQL (example):
SELECT u.region, COUNT(DISTINCT o.user_id) AS pay_users, SUM(o.amount) AS total FROM users u JOIN orders o ON u.id = o.user_id WHERE o.status = 'paid' AND o.created_at >= '2026-11-01' AND o.created_at < '2026-12-01' GROUP BY u.region;
- Provide approximate table sizes and a representative time window.
Desired outputs 1) EXPLAIN of the baseline query. 2) One or more verified alternatives (rewritten query and/or candidate index). 3) A migration plan for adding an index with rollback and monitoring steps.
Step-by-step workflow you can follow
1) Fix the scope and permissions - Use a test database or a read-only connection. Provide schema, indexes, row counts, and the original SQL. 2) Capture the baseline - Run EXPLAIN or EXPLAIN ANALYZE (if supported) on the test dataset; save the plan, execution time, and returned rows sample. 3) Analyze the plan (key checks) - Look for full table scans, bad index choices, large temporary tables, or filesorts. Verify join order and cardinality estimates. 4) Create candidate solutions - Try query rewrites first: apply time filters as early as possible, replace COUNT(DISTINCT) with pre-aggregation if possible, or use EXISTS/derived tables to reduce duplication. - If indexing is needed, propose candidate indexes with explicit column order and justification (covering index vs filter-leading index), and estimate write/storage cost. 5) Validate on the same test data - Compare EXPLAIN and measured runtime for each alternative under similar conditions; run multiple times to reduce noise (caching, concurrency). 6) Prepare a migration (if adding an index) - Include a non-blocking/online create method if the engine supports it, a backup/verification step, a roll-forward window, a DROP INDEX rollback command, and checkpoints for monitoring. 7) Document results and acceptance criteria - Save original SQL, all EXPLAIN outputs, runtime comparisons, and a short description of how to verify correctness in production.
Concrete suggestions based on the example
- Rewrite pattern: instead of COUNT(DISTINCT o.user_id) across the join, pre-aggregate orders to one row per user for the time window and status, then join to users. This often reduces rows processed by the grouping step.
- Candidate index types (explanatory only):
- Index A: (status, created_at, user_id) — aims to filter by status, then by time, then be useful for grouping/joins. - Index B: (created_at, status, user_id) — if time range is the primary filter and selective.
- Pick the index that matches the most selective predicates and the join order. Consider write amplification and storage cost before applying.
Validation checklist (must-do checks)
- Input validation: confirm field names, keys and existing indexes; confirm status domain values (e.g., 'paid').
- Semantic validation: compare results from a small, hand-checked sample between baseline and rewritten queries to ensure identical business totals.
- Performance validation: compare EXPLAIN (cost estimates) and real runtimes; consider repeating tests to factor out caching effects.
- Migration risk assessment: estimate index build time, possible lock impacts, and whether an online build is available; plan monitoring metrics.
Common limitations and risks
- Some DDL or index builds may block writes or cause long backfills; always include a rollback plan and choose a maintenance window if needed.
- Correct SQL syntax does not guarantee correct business results; mismatched grain, NULL behavior, or join multiplicity often cause incorrect totals.
- EXPLAIN output is engine- and version-specific; run comparisons on the same engine/version as production to get meaningful conclusions.
Final recommendations
- Follow the iterative pattern: define a bounded test input, produce SQL and plan, validate results on representative data, and only then prepare a reversible production deploy.
- Keep artifacts for each change: original SQL, EXPLAIN outputs, runtime measurements, and verification samples. Require human approval before any write/DDL change.
(End)