SQL Query Optimizer

Problem

Slow query diagnosis often gets stuck between adding an index, rewriting the query, and tuning the database. Function-wrapped columns such as YEAR(created_at) or LOWER(name), leading-wildcard LIKE '%prefix', correlated subqueries, deep OFFSET pagination, and large UPDATE / DELETE transactions can all make execution plans diverge from expectations.

How It Works

The skill works with SELECT, INSERT, UPDATE, DELETE, and DDL statements, optionally using database type, table schemas, index lists, execution plans, and performance goals. Key steps:
- Gather context: identify missing schema, indexes, EXPLAIN output, and statistics for MySQL, PostgreSQL, SQL Server, Oracle, SQLite, or MariaDB.
- Detect anti-patterns: scan filtering, joins, subqueries, sorting, pagination, aggregation, set operations, locking, and DML issues, labeled by severity.
- Explain execution plans: analyze signals such as seq scan, filesort, Using temporary, Sort Method: external merge, Key Lookup, and TABLE ACCESS FULL to locate the cost driver.
- Produce structured output: return a Problem / Impact / Suggestion table, a rewritten SQL query, and index recommendations such as B-tree, composite, partial, or expression indexes.

Boundaries

It is useful for query diagnosis, rewrite suggestions, and index strategy discussion, but it does not replace validation in a real data environment. Index benefit depends on row count, selectivity, concurrency, buffer pool, and statistics; some rewrites change semantics, performance characteristics, or maintenance cost, so each change should be evaluated before production use.