SQL Optimizer

Analyze, diagnose, and optimize SQL queries for better database performance.

Scope

• Input: Any SQL query (SELECT, INSERT, UPDATE, DELETE, DDL), with optional context (table schema, index list, execution plan, database type).
• Output: Structured Markdown table with Problem, Impact, and Suggestion columns; plus rewritten SQL where applicable.
• Database support: MySQL, PostgreSQL, SQL Server, Oracle, SQLite, MariaDB.

Workflow

Step 1: Gather Context

When a user provides a SQL query, first identify what information is already given vs. what's missing. Ask for or infer:

1. Database type — MySQL, PostgreSQL, SQL Server, Oracle, SQLite
2. Table schemas — column types, NULLability, constraints
3. Existing indexes — via SHOW INDEX, pg_indexes, sys.indexes, USER_INDEXES
4. Execution plan — EXPLAIN ANALYZE, EXPLAIN (ANALYZE, BUFFERS), SET STATISTICS IO ON, DBMS_XPLAN
5. Cardinality & data distribution — estimated row count, value distribution
6. Query performance goals — reduce latency, reduce IO, reduce temp-disk usage, reduce lock contention

If the query alone is given without context, use SQL pattern analysis to detect common anti-patterns.

Step 2: Detect Performance Problems

Scan the query for all known anti-patterns:

Step 3: Analyze Execution Plan (if provided)

Parse and explain these key plan metrics:

Execution Plan Keywords by Database

Step 4: Output — Markdown Table

Format every diagnosis as a Markdown table with three columns:

| Problem | Impact | Suggestion |
|---------|--------|------------|
| … | … | … |

Sort rows by severity (Critical → High → Medium → Low).

Step 5: Rewrite SQL (when applicable)

After the diagnosis table, provide a rewritten query with:

1. The original query in a SQL code block
2. The optimized query with annotations
3. A brief explanation of each change and its expected impact

Output Template

Always produce analysis in this structure:

1. Diagnosis

2. Rewritten Query

-- Original
SELECT * FROM orders WHERE YEAR(created_at) = 2024;

-- Optimized
SELECT * FROM orders
WHERE created_at >= '2024-01-01'
  AND created_at <  '2025-01-01';

Explanation: Replaced function-on-column YEAR(created_at) with range predicate, enabling index seek on created_at. Expected: seq scan → index range scan.

3. Index Recommendations

4. Execution Plan Summary (if plan provided)

Index Strategy Reference

Index Type Selection

Cardinality & Selectivity

Index Maintenance

Database-Specific Optimization

MySQL

EXPLAIN ANALYZE SELECT ... \G          -- MySQL 8.0.18+, actual runtime

• Using index = covering index, no table access — good
• Using where = rows filtered after storage engine read — check selectivity
• Using temporary = temp table created — avoid for large data
• Using filesort = sort outside index — add composite index covering ORDER BY
• Using index condition = index condition pushdown (ICP) — good, MySQL 5.6+
• Impossible WHERE = WHERE always false — dead code
• Covering index: If all queried columns are in the index, MySQL avoids heap lookup
• MRR (Multi-Range Read): Sorts by PK before lookup — reduces random IO
• BKA (Batched Key Access): Batches join lookups — reduces round trips
• Buffer Pool: INNODB_BUFFER_POOL_SIZE should fit working set

PostgreSQL

EXPLAIN (ANALYZE, BUFFERS, TIMING) SELECT ...;

• rows vs actual rows mismatch (> 10x) → stale stats, run ANALYZE
• Parallel Seq Scan = normal for tables > 1 GB with sufficient max_parallel_workers_per_gather
• Sort Method: external merge Disk → increase work_mem
• CTEs are optimization fences (PG < 12): not inlined, always materialized. PG 12+ inlines CTEs, PG 16+ allows MATERIALIZED / NOT MATERIALIZED hint
• NOT IN on nullable column → always returns 0 rows. Use NOT EXISTS
• Autovacuum health critical: bloat → more page reads per scan
• pg_stat_user_tables.n_dead_tup > 20% of n_live_tup → vacuum behind
• pg_stat_user_indexes → check idx_scan to find unused indexes
• pg_stat_statements → identify most time-consuming queries

SQL Server

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
SELECT ...;

• PAGEIOLATCH_SH wait type = disk IO bottleneck (insufficient buffer pool)
• LCK_M_* wait types = blocking / lock contention
• CXPACKET wait = parallelism skew (one thread slower than rest)
• Table Scan vs Clustered Index Scan — both full scans, but CI scan may be narrower if clustered PK is narrow
• Key Lookup (clustered) / RID Lookup (heap) = non-covering index → expensive per-row lookups. Add INCLUDE columns
• Missing index suggestions → green text in graphical plan, or sys.dm_db_missing_index_details
• Parameter sniffing → plan cached for first parameter's distribution. Fix: OPTION (RECOMPILE) or OPTIMIZE FOR UNKNOWN
• NOLOCK hint = dirty reads. Prefer READ COMMITTED SNAPSHOT database setting

Oracle

-- After query runs, get actual plan:
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST +OUTLINE'));

• TABLE ACCESS FULL — not always bad; Oracle multiblock reads are efficient for large tables
• INDEX UNIQUE SCAN → ideally 1 row returned
• INDEX RANGE SCAN → check number of rows scanned
• NESTED LOOPS effective when driving set is small (< 10k rows) and inner table has unique index access
• HASH JOIN preferred for large unindexed joins
• SORT (AGGREGATE) without GROUP BY → aggregates all rows, verify cardinality first
• Buffer gets vs consistent gets in AUTOTRACE TRACEONLY STATISTICS
• SQL Profile / SQL Plan Baseline — lock in good plan without code changes
• Star transformation — automatic rewrite for star-schema joins

Common Anti-Pattern Quick-Fix

Examples

Example 1: Function on Column

Query:

SELECT * FROM orders
WHERE YEAR(created_at) = 2024
ORDER BY total_amount DESC;

Diagnosis:

Rewritten:

SELECT id, user_id, total_amount, status
FROM orders
WHERE created_at >= '2024-01-01'
  AND created_at <  '2025-01-01'
ORDER BY total_amount DESC;

Index:

CREATE INDEX idx_orders_created_amount ON orders(created_at, total_amount DESC);

Example 2: Missing Join Index

Query:

SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-06-01'
GROUP BY u.id, u.name;

Diagnosis:

Rewritten:

SELECT u.name, o.order_count
FROM users u
LEFT JOIN (SELECT user_id, COUNT(*) AS order_count
           FROM orders
           GROUP BY user_id) o ON u.id = o.user_id
WHERE u.created_at > '2024-06-01';

Index:

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_users_created ON users(created_at);