A Blind Spot Shared by Most Small Teams

If you ask an indie developer or the tech lead of a small team, "Do you have slow query logging enabled on your database?" most will answer, "Yes, it's on."

Then ask, "When was the last time you seriously analyzed it?" The answer usually gets vague.

That's nobody's fault. Slow query log analysis is a classic case of "important but not urgent":

• Log files can easily run to hundreds of MB—reading them by hand is unrealistic
• To determine what index a slow query needs, you have to understand the table structure, data distribution, query patterns, and execution plans
• The cost of adding the wrong index is significant—slower writes, space bloat, and even the optimizer picking the wrong plan
• Business requirements always come first, so this task never makes it onto the schedule

The result: pages get slower and slower, everyone reflexively blames "data volume growth," machines get added and configs upgraded—money spent, problem remains. Meanwhile, the real answer might just be sitting in the log—a single SQL statement missing an index, doing a full table scan over millions of rows.

What AI Can Do Here

Based on my own practice, the role AI can play in slow query analysis is that of "a junior DBA who understands execution plans":

1. Log preprocessing and clustering: Deduplicate and categorize hundreds of slow queries by SQL fingerprint, identifying the Top N high-frequency, high-cost patterns
2. Index recommendation generation: Combined with the table structures (DDL), produce recommended indexes for each query pattern, along with explanations of why
3. Risk flagging: Point out which indexes may be redundant and which recommendations need business judgment (e.g., low-selectivity columns)
4. Executable script output: Directly generate reviewable CREATE INDEX statements, complete with rollback statements and deployment notes

The key point: AI doesn't make decisions for you, but it does all the dirty work between "raw logs" and "an executable plan." You only do the final review—and that review is an order of magnitude faster than analyzing from scratch.

My Actual Workflow

The whole process has four steps, based on my own practice:

Step 1: Gather the raw material. Export the slow query log from the database (for MySQL, it's better to do a preliminary aggregation with mysqldumpslow or pt-query-digest first), and also export the DDL of the involved tables (SHOW CREATE TABLE).

Step 2: Feed it to AI. Put the aggregated Top slow queries and DDL together into the prompt. Keep the length under control—prioritize the Top 20 query patterns by execution time; going too long actually dilutes quality.

Step 3: Review the recommendations. The AI will output a diagnosis, recommended indexes, and redundancy risks for each query pattern. Don't skimp on this step—especially for write-heavy tables, always ask "what's the impact of this index on writes?"

Step 4: Validate and deploy. Validate before-and-after execution plans with EXPLAIN in a staging environment, deploy only after confirming the index is being used, and run the DDL during off-peak hours.

Prompt Template

Ready to use—just replace the placeholders with your actual content:

你是一位资深数据库性能优化工程师。请根据我提供的慢查询日志聚合结果和表结构，给出索引优化建议。

## 慢查询日志（已按耗时排序）
{粘贴 pt-query-digest 或 mysqldumpslow 的聚合结果，建议 Top 20}

## 涉及表的 DDL
{粘贴 SHOW CREATE TABLE 输出}

## 要求
1. 逐条分析每个慢查询：指出当前为什么慢（全表扫描/文件排序/临时表等）
2. 给出建议的 CREATE INDEX 语句，并解释每列的顺序理由（最左前缀原则）
3. 检查建议之间是否有冗余索引，指出可合并的
4. 对写多读少的表，评估索引对写入性能的影响
5. 每条建议标注风险等级：低（可直接上）/ 中（建议预发验证）/ 高（需结合业务判断）
6. 最后汇总为一份可执行的上线清单，包含回滚语句
7. 不确定的信息（如数据分布、列基数）请明确列出，提醒我补充验证，不要猜测

Point 7 is important—forbidding the AI from guessing data distribution significantly reduces "plausible-sounding but actually ineffective" recommendations.

Before and After: A Time Accounting

I ran a before-and-after comparison on a project I maintain (a dozen-plus tables, including a log table growing by millions of rows per day), involving no customer data—just my own time records:

Beyond the time savings, there are two hidden benefits:

• Avoided trial-and-error costs. In the past when I designed indexes myself, I hit the pitfall of "created an index the optimizer wouldn't use" and wasted an entire evening troubleshooting it. The AI proactively flagged a case of implicit type conversion invalidating an index.
• Knowledge accumulation. Every AI recommendation comes with an explanation, and after one round, everyone else on the team leveled up their understanding of execution plans. This beats pure outsourcing to a DBA—the DBA leaves after the fix, and no knowledge stays behind.

As for cost, a complete analysis consumes a small amount of tokens; check your model's official pricing page for exact figures—but compared to three days of labor, this math doesn't need much calculation.

A Few Points from Practice

1. Don't dump the entire log in at once. Aggregate with tools first and feed only the Top patterns—quality is much higher.
2. Double-check the column order of AI-recommended indexes. Equality conditions first, range conditions after—this is something it occasionally mixes up.
3. Never skip staging validation. However accurate the AI is, the before-and-after EXPLAIN comparison is the final judge.
4. Keep the conversation context. For new slow queries later, continue in the same session—the AI already has the table structure background, making it much more efficient.

Final Thoughts

Database optimization used to be "a privilege of companies with dedicated DBAs." It's not that small teams don't have the need—it's that the analysis cost was so high they just had to endure it. AI has lowered that barrier by a huge margin—you still need someone who understands the fundamentals to make the final call, but the journey from raw logs to a reviewable plan has been compressed from days to hours.

If you want to implement this workflow in your project and need a stable, well-managed model API endpoint, you can try this platform: https://api.thistoken.ai/register—sign up to get access and run the prompt template above right away.

---

Ready to try it yourself? Sign up at https://api.thistoken.ai/register to get your API key and start building.