Slow Query Tuning with AI: From Two Days to Twenty Minutes
An All-Too-Familiar Morning
Friday morning, you open your database monitoring dashboard and discover that a promotional page launched yesterday has quadrupled query response times. You export the slow query log and take a look: over three hundred entries, with the longest one taking 11 seconds to execute.
What comes next is the traditional routine: reading through the logs one by one, locating the corresponding SQL, guessing whether it's an index issue, running EXPLAIN against the database, digging up the table creation statements to check the indexes... Working through two hundred log entries carefully eats up an entire day. And if you happen to be the only person on the team who knows SQL optimization (the norm for independent developers and small teams), this task gets pushed to the weekend.
What makes it worse is that the slow query log has a very low density of genuinely useful information. Tons of duplicate patterns, queries that differ only in parameters but share the same structure, redundant JOINs clearly generated by a framework—filtering through them manually, seventy percent of your time goes to "classification," and only thirty percent goes to actual "optimization decisions."
Classifying, extracting, cross-referencing table structures and writing explanations—this is exactly the kind of work AI excels at.
My Current Workflow: Three Steps, Twenty Minutes
Step 1: Preprocess and Compress the Input (5 minutes)
Don't throw the raw logs directly at the AI. First, use a simple script (awk or Python works fine) for deduplication and aggregation:
-- Similar to the pt-query-digest approach, write a simplified version yourself
SELECT digest, COUNT(*), MAX(query_time), SUM(rows_examined)
FROM slow_log GROUP BY digest ORDER BY SUM(rows_examined) DESC;After aggregating three hundred log entries, you're typically left with only twenty or thirty "query patterns." This step should be done regardless of whether you use AI—it cuts the problem size down by 90%.
Step 2: Feed It to the AI with Context (10 minutes)
The key point: SQL text alone is of limited value to the AI; you must include the table structures and EXPLAIN results. My approach is to bundle three things into the prompt: the DDL (output of SHOW CREATE TABLE), the deduplicated slow query list, and the EXPLAIN results for representative SQL statements.
Here's a copyable template—take it and adapt it:
你是一位资深数据库优化工程师。请分析以下MySQL慢查询,输出优化建议。
## 背景信息
数据库版本:8.0
表数据量:orders 约 800万行,users 约 50万行
业务场景:电商订单列表页,读多写少
## 表结构
<粘贴 SHOW CREATE TABLE 的完整输出>
## 慢查询(已按扫描行数排序)
<粘贴去重聚合后的SQL,附执行次数和最长耗时>
## EXPLAIN结果
<粘贴代表性SQL的EXPLAIN输出>
## 请按以下格式输出
1. 每条查询的问题诊断(索引缺失/索引失效/写法问题/架构问题)
2. 具体优化方案:需要创建的索引给出完整DDL,写法问题给出改写后的SQL
3. 每项改动的预期收益和潜在风险(如写入性能影响、锁表风险)
4. 按投入产出比排序,标注哪些建议可以立即执行、哪些建议先在从库验证
注意:不要给出泛泛建议(如"建议加索引"),所有建议必须落到具体DDL或SQL。Step 3: Manual Verification (5 minutes)
For the index DDLs the AI suggests, I don't execute them directly. Instead, I copy them to a test database and run them to confirm the execution plan actually changed. These 5 minutes can't be skipped—AI will occasionally suggest redundant indexes or overlook existing covering indexes. But verifying a ready-made DDL is far faster than analyzing from scratch.
Before and After: Doing the Math
Using this batch of three hundred slow queries as an example:
| Task | Manual Only | AI-Assisted |
|---|---|---|
| Log deduplication and classification | 2–3 hours | 5 minutes (script) |
| Identifying issues one by one | 4–6 hours | 10 minutes (AI batch diagnosis) |
| Writing optimization plans | 2–3 hours | Merged into the previous step |
| Manual verification | 1 hour | 30 minutes |
| Total | ~2 workdays | ~45 minutes |
Based on a rough estimate of an engineer's daily cost, a single round of slow query remediation drops from two person-days to under an hour. More importantly, it frees up your attention: the saved time goes toward verifying the solutions and watching the monitoring curves after deployment, instead of being consumed by mechanical classification.
On the cost side, a full round of analysis (a few dozen SQL statements plus DDL context) doesn't consume many tokens—typically on the order of a few cents to a few dozen cents; check the official pricing page of whichever model you use for specifics. Compared to the two person-days saved, it's negligible.
A Few Practical Tips
The quality of AI output depends on the quality of the context. Without table structures, it can only guess; with DDL and EXPLAIN, it can pinpoint specific issues like "this query performs a function computation on the status column, which invalidates the index."
Watch out for hallucinations. AI will occasionally reference index names that don't exist, or suggest creating indexes that heavily duplicate existing ones. The verification step is mandatory, not optional.
Turn your templates into team assets. We store the prompt template above in a shared document, appending background information each time we encounter a new scenario (read/write splitting, sharding). When new team members take over slow query remediation, they no longer need to "spend three months getting familiar with the business first."
Final Thoughts
Slow query remediation is essentially a transformation process of "a large volume of low-information raw data → a small number of high-value decision recommendations," which is precisely the classic shape of AI-assisted work. What independent developers lack isn't optimization knowledge—it's the time to apply that knowledge across three hundred log entries. Hand the mechanical parts to the AI, and keep the decision-making for yourself—that's the most cost-effective division of labor for a small team.
If you don't have a go-to AI API yet, you can register at https://api.thistoken.ai/register, run the template above, and the next time a slow query alert fires, you'll be done in twenty minutes.
---
Ready to try it yourself? Sign up at https://api.thistoken.ai/register to get your API key and start building.
Хотите попробовать Token.AI?
Создайте API Key уровня проекта, включите каналы в консоли и настройте маршрутизацию, бюджеты и журналы аудита.
注册 ThisToken.AI 并获取 API Key