An Old Problem for Managers: An AI-Assisted Workflow for Database Index Optimization

Anyone who has led a team has probably experienced this scenario: one day the business group chat explodes — "checkout is so slow," "the list page won't load" — and upon investigation, it's slow database queries. So you pull in the DBA (if you have one) or some backend engineer, who manually digs through slow query logs and relies on experience to decide whether to add an index and on which field.

For small teams, the problem is even more acute:

• No dedicated DBA — index optimization depends on a backend engineer's gut feeling, quality varies widely, and when they leave, the experience leaves with them;
• No record of index decisions — why an index was added and what impact it had all lives in chat history; later, no one can explain it;
• Adding indexes is itself risky — adding an index to a large production table can lock the table or slow down writes, yet there's often no process for who approves it and what execution window to use;
• The same mistakes get repeated — a new project launches, the slow query problem comes back again, and none of the previous analysis carries over.

I recently rolled out a workflow in my team: "AI reads slow query logs → generates index recommendations → human review → off-peak execution → regression verification." This turned something that depended on individual experience into a repeatable team process. Here's how it works.

What AI Can Do in This Workflow

Let me set the boundaries first: AI does not deploy indexes for you. Instead, it compresses the entire analysis phase from "hours" to "minutes" and produces structured, reviewable recommendation documents. What it does includes:

1. Parsing slow query logs: Extracting high-frequency SQL, abnormal scan row counts, and WHERE/JOIN/ORDER BY conditions lacking indexes from a pile of raw logs;
2. Proposing candidate indexes: Based on the table schema (DDL), providing specific CREATE INDEX statements along with justifications;
3. Assessing side effects: Flagging issues like write amplification, low index selectivity, and redundant indexes;
4. Generating review documents: Producing an explanation anyone on the team can understand, making it easy for managers to sign off and archive.

Decision-making and execution authority always stay with humans — this is the prerequisite for making this workflow work.

The Workflow: Four Steps

Step 1: Sanitize and export slow query logs. Enable the slow query log (e.g., MySQL's slow_query_log), or use pt-query-digest for a summary, and replace sensitive business information in database and table names. This step is critical for risk control — don't throw real data directly at an external model.

Step 2: Feed the log summary + table DDL to the AI. Export the DDL using SHOW CREATE TABLE. The focus of the AI's analysis isn't individual SQL statements, but patterns: which queries share the same filter conditions, and which sorts lack index support.

Step 3: Human review of recommendations. For each index recommendation the AI outputs, a backend engineer confirms selectivity, checks for redundancy with existing indexes, and validates in a test environment using EXPLAIN. AI makes mistakes — like suggesting an index on a low-cardinality field (e.g., a gender field). This step must have a human gatekeeper.

Step 4: Off-peak execution + archiving. For large tables, use ALGORITHM=INPLACE, LOCK=NONE, execute during off-peak hours, and record slow query counts before and after. Archive the AI-generated review document in the team knowledge base, so similar issues can be looked up directly next time.

A Reusable Prompt Template

你是一位资深数据库优化顾问。请根据我提供的慢查询日志摘要和表结构，
输出结构化的索引优化建议。

【输入一：慢查询日志摘要】
<paste_here: pt-query-digest 输出或慢查询片段，已脱敏>

【输入二：相关表结构】
<paste_here: SHOW CREATE TABLE 结果>

请按以下格式输出：
1. 问题诊断：列出最值得优先处理的 3-5 个慢查询模式，
   说明各自的总耗时占比、扫描行数与返回行数比例；
2. 索引建议：每条建议包含
   - 具体的 CREATE INDEX 语句
   - 依据（对应哪些查询的哪些条件）
   - 预期改善（基于EXPLAIN推断）
   - 风险提示（写放大、锁表风险、是否与现有索引冗余）；
3. 不建议做的事：明确指出哪些查询不适合加索引，应考虑改写SQL或加缓存；
4. 执行顺序：按「收益/风险比」排序，标注哪些建议先在测试环境验证。

注意：如果信息不足以判断，请直接说明缺失什么，不要猜测。

The last sentence is crucial — not giving the AI room to "make things up" is the simplest and most effective way to control output quality.

Before vs. After AI

To be clear: the AI's recommendation error rate is not zero. We've seen it suggest an index on a field with only 3 distinct values, and we've seen it overlook an existing composite index that could cover a query. But all these issues were caught during the human review step, and the analysis time it saves plus the documentation it produces are tangible value for managers.

Cost and Tool Selection

Mainstream large language models at mid-tier levels are more than sufficient for this kind of analysis task — you don't need a top-of-the-line model. If multiple people on the team share usage, a unified API access is more cost-effective than individual subscriptions; refer to the official pricing page for details. When log volume is large, feed it in batches rather than stuffing everything into one super-long context — batching actually helps the model focus on the high-frequency queries in each batch.

Final Thoughts

Index optimization has never been the flashiest technical topic, but it's one of the easiest places for small teams to crash and burn. Handing this over to the combination of "AI analysis + human review + process archiving" gives managers predictable quality, traceable decisions, and reusable experience — precisely the three things that are scarcest as a technical team scales.

If you're also building AI workflows for your team, consider accessing multiple models through a unified API platform to avoid betting on a single model. Registration link: https://api.thistoken.ai/register

---

Every example in this post runs with a single API key — get yours at https://api.thistoken.ai/register and start in minutes.