Query Optimization With AI

TL;DR

• AI can analyze slow queries, suggest indexes, and rewrite SQL. It will also suggest wrong or dangerous things.
• Use AI for ideas. You verify: Does this fit our schema? Our workload? Our risk tolerance?
• Never run AI-suggested changes in prod without testing. Ever.

Slow queries are a fact of life. So are DBAs who've seen the same patterns a hundred times. AI has seen patterns too — in training data. It can suggest optimizations quickly. It can also suggest optimizations that don't fit your context, violate constraints, or make things worse. Your job: use the ideas, verify the execution.

What AI Does Reasonably Well

Explaining execution plans:

• "Why is this query slow?" Paste the plan. AI can interpret. Useful for learning and triage.
• Don't trust blindly. Verify against your actual DB behavior.

Index suggestions:

• "Add an index on (a, b)" — AI sees the filter and suggests. Often right for simple cases.
• Check: Does the index already exist (different name)? Does it fit your write load? Would it bloat?

Query rewrites:

• "Use a CTE instead of a subquery" or "Avoid SELECT *" — AI knows common optimizations.
• Test. Rewrites can change semantics. EXPLAIN before and after. Compare results.

Pattern recognition:

• "This looks like an N+1" or "Missing index on join column." AI can spot common anti-patterns.
• Good for triage. Not a replacement for profiling.

What AI Gets Wrong

Context blindness:

• AI doesn't know your table sizes, cardinality, or growth rate. It suggests based on averages.
• Small table? Index might not matter. Huge table? Different indexing strategy. You know.

Constraint violations:

• "Drop this index" — maybe it's needed for another query. "Add this column" — maybe it doesn't exist or has different semantics.
• AI doesn't know your full workload. You do.

Vendor and version specifics:

• PostgreSQL vs. MySQL vs. SQL Server — behavior differs. AI might mix them.
• Always verify syntax and behavior for your engine.

Risky operations:

• AI has been known to suggest things like "DROP TABLE" or destructive migrations. Never run unchecked.
• Read every suggestion. Understand it. Test it.

The Workflow

1. Get suggestions — Paste query, plan, or schema. Ask AI for optimization ideas.
2. Filter — Which suggestions are plausible? Which are wrong for your context?
3. Test — Run in dev/staging. Compare execution time, plan, and result correctness. Benchmark.
4. Apply — Only then consider prod. With a rollback plan.

Building Your Edge

• Workload awareness. You know what runs when, what's critical, what can wait. AI doesn't.
• Schema ownership. You know the constraints, the legacy, the "don't touch that" zones. Document them. AI can't.
• Relationship with devs. You help them write better queries before they hit prod. AI can suggest; you teach.

-- AI suggests: CREATE INDEX idx_user_created ON orders(user_id, created_at);
-- You verify: Does this fit write load? Already exist? Bloat risk?
-- Test in staging. EXPLAIN before/after. Then consider prod.

Do This Next

1. Run one real slow query through AI — Get suggestions. Test each. Document what worked and what didn't. Build a personal playbook.
2. Create a "never run without testing" policy — For you and for anyone who might use AI for DB work. Enforce it.
3. Document your schema context — Table sizes, growth, hot paths. When you use AI, include that context in your prompts. Better input = better output.