Automating MySQL Slow Query Triage with an AI Database Agent

A query regression landed in Tuesday's deploy. Checkout latency crept from 180 ms to 900 ms at p95, a few customers complained, someone bumped the RDS instance size, and the ticket that said "check the slow log" sat in the backlog for two weeks. Nobody on the team is a DBA, and reading a MySQL slow query log is nobody's favorite Thursday.

That timeline is the real cost of slow queries at most mid-market companies. The diagnosis isn't the hard part: part one of this series showed the five patterns behind almost every slow query, and part two walked through finding them with the slow log, performance_schema and EXPLAIN ANALYZE. Diagnosis is teachable. The cost is the gap: the days or weeks between a regression shipping and a person sitting down with the evidence.

This part covers closing that gap. CloudThinker watches your MySQL slow-query surface continuously, investigates regressions the day they ship, brings you index and query proposals with the evidence attached, and (the part your security review will ask about first) refuses to change anything without your explicit approval.

---

Why a better dashboard doesn't close the gap

The usual next step after part two is a dashboard: graph the top digests, alert on p95. That moves detection forward by a few days and leaves the expensive part untouched. When the alert fires, someone still has to pull the digest, read the plan, rule out locking, check whether the deploy changed the query shape, and work out which fix the evidence supports.

That work is branching. A spike caused by a new query shape needs a code fix. A spike caused by table growth needs an index. A spike caused by a long transaction needs neither, and adding an index there just taxes every write. A threshold alert can't tell those apart, and a scripted runbook can only follow the branches someone predicted. The investigation is the step that needs judgment, so it's the step that waits for a human with a free afternoon.

Connecting MySQL: a read-only user, about five minutes

01Database

MySQL

• Statement digestsperformance_schema, sys schema
• Slow query logThresholds and captured statements
• Live sessions and replicationPROCESS, REPLICATION CLIENT

SELECT, PROCESS, REPLICATION CLIENT

02Investigation

CloudThinker

• Baseline every digestLatency and plan, week over week
• Investigate regressionsPlans, rows examined, lock waits, deploys
• Propose the fixIndex DDL with evidence and rollback

No plugin, no binlog tap

03Where the team works

Chat and tickets

• Slack or TeamsFindings and approvals in the thread
• JiraFollow-ups with the case file linked

Nothing new to watch

CloudThinker connects to MySQL the way you'd want any third party to: with a dedicated database user that can read metadata and performance data, and nothing else. No plugin in the server, no binlog tap, no write privileges at connection time. You create the user, paste the endpoint and credentials into CloudThinker, and the first analysis starts. The MySQL connection guide has the exact statements. The shape is:

CREATE USER 'cloudthinker'@'%' IDENTIFIED BY '<strong-password>';
GRANT SELECT, PROCESS, REPLICATION CLIENT ON *.* TO 'cloudthinker'@'%';

SELECT covers performance_schema, the sys schema and information_schema. PROCESS lets CloudThinker see the live processlist and run EXPLAIN on queries it didn't issue. REPLICATION CLIENT covers replication lag checks. Your security team can read that grant in one line and see exactly what is and isn't possible. If you'd rather not grant SELECT on application schemas, scope it to performance_schema.* and sys.* and accept that plan checks will need a replica.

The whole flow takes about five minutes against RDS, Aurora MySQL, Cloud SQL or self-hosted MySQL 8.x.

What CloudThinker watches continuously

Everything in part two, on a loop instead of an afternoon:

• Statement digests in performance_schema. CloudThinker reads events_statements_summary_by_digest on a rolling basis, so it ranks queries by total time (count times latency), not only the single worst offender. The 3 ms query running 500,000 times a day shows up next to the 40-second report.
• The slow query log, properly configured. If your long_query_time is still the 10-second default, the first finding is usually that your slow log is effectively off: everything between "annoying" and "catastrophic" is invisible. It proposes a sane threshold before it proposes anything else.
• Execution plans and their regressions. Full table scans, filesorts, temporary tables spilling to disk, index-defeating implicit casts: the patterns from part one, detected from digest statistics and confirmed with EXPLAIN.
• Baselines per query digest. This is the part a point-in-time audit can't do. CloudThinker knows what each digest's latency looked like last week, so Tuesday's deploy regressing one endpoint's query is a finding on Tuesday, with the before and after plans side by side, not a mystery two weeks later.
• The supporting cast. Lock waits and contention hot spots, unused and redundant indexes, buffer pool pressure, replication lag.

One regression, investigated

01Signal

Digest moves

A query digest is slower than its own baseline after a deploy.

02Hypotheses

Investigate

Plan change, data growth, spill or lock wait, each checked against evidence.

03Proof

Before and after

Current plan, proposed index, projected plan and write cost.

04Decision

Approval

A named person approves the exact statement, or it waits.

Back to Tuesday's checkout regression. Here's what the investigation looks like when it runs the same afternoon. The numbers are illustrative.

The signal. The digest behind the order-lookup endpoint went from 4 ms average to 610 ms, starting 14:20, twelve minutes after a deploy event. That timing is the first clue, and it's why change correlation runs early.

The hypotheses. Three readings fit "same endpoint, suddenly slow":

• Data growth past a tipping point. Predicts the same digest with gradually rising rows examined. A step change at deploy time argues against it.
• Lock contention. Predicts high lock time and variable latency. Checkable in the digest table.
• A new query shape from the deploy. Predicts a digest that didn't exist before 14:08.

The check. One query against the digest table tests the third reading directly, because every digest records when it was first seen:

SELECT LEFT(DIGEST_TEXT, 80)            AS query,
       FIRST_SEEN,
       COUNT_STAR                       AS calls,
       ROUND(AVG_TIMER_WAIT / 1e9, 1)   AS avg_ms,
       ROUND(SUM_LOCK_TIME / 1e9, 1)    AS lock_ms,
       SUM_ROWS_EXAMINED / COUNT_STAR   AS rows_per_call
FROM performance_schema.events_statements_summary_by_digest
WHERE FIRST_SEEN > NOW() - INTERVAL 1 DAY
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 5;

What came back. A new digest, first seen at 14:09: SELECT ... FROM orders WHERE external_id = ?, about 1.9 million rows examined per call and near-zero lock time. Locking is out. Growth is out, because the old digest for the same lookup simply stopped appearing. EXPLAIN on a captured example shows possible_keys: idx_external_id, key: NULL, type: ALL. external_id is a VARCHAR, and the new code binds it as an integer. MySQL has to convert every row's value to compare, so the index can't be used.

The proposal. The finding that lands in Slack says exactly that, and proposes the fix the evidence supports: bind external_id as a string in the order service (a one-line code change, linked to the deploy that introduced it), with the before and after plans attached. It explicitly does not propose a new index, because the right index already exists. A threshold alert would have paged someone. A dashboard would have shown a red line. Neither would have said "your ORM change defeats idx_external_id."

Proposals come with evidence, not vibes

An index suggestion you can't verify is a liability: every index you add taxes every write and competes for buffer pool. So each recommendation carries its case file:

• the query digest and its call sites (count, total time, rows examined versus rows returned);
• the current EXPLAIN output showing the scan or filesort;
• the proposed index definition, and why those columns in that order;
• the projected plan after the index, checked with EXPLAIN ANALYZE-style measurement where a safe replica or staging copy is available;
• the write-amplification cost: how many inserts and updates per day will now maintain this index.

For drops it's the same standard in reverse. An index is flagged unused only after sys.schema_unused_indexes and digest history agree over a meaningful window, and the proposal makes it invisible first, a reversible experiment, before ever suggesting removal.

No surprise DDL

CloudThinker acts within Auto Mode, so you decide what runs on its own and what waits for approval. Two more rules apply. First, schema changes need a separate credential. Executing CREATE INDEX, ALTER TABLE or DROP INDEX at all requires you to grant a scoped write user; the read-only connection user physically can't run DDL. Second, everything lands in an audit trail: finding, evidence, proposal, approver, statement executed, timestamp. When someone asks "who added that index and why," the answer is one link.

What a first analysis typically finds

Illustrative numbers: a composite of first scans on mid-market SaaS databases in the 100 to 500 GB range. Yours will differ.

The shape repeats across environments: one or two missing or misused indexes explain most of the user-facing p95, one chatty endpoint dominates total load, and the slow log wasn't catching any of it. None of these findings is exotic. Every one is detectable with part two's playbook. The difference is that this table refreshes continuously, and each row arrives with an approval-gated fix attached instead of a ticket that waits two weeks.

Why this beats the traditional approach

The deeper difference is memory. Part two's tools answer any question you think to ask, once. A baseline per digest answers the question nobody asked: what changed since last week, and why.

Prompts to try in your first session

CloudThinker is conversational. You ask in plain language and get answers that cite performance_schema evidence you can verify yourself:

"What are our top 10 queries by total execution time this week, and which ones regressed after Tuesday's deploy?"

"Why is the order search doing a full table scan? Propose an index and show me the before and after plans. Propose only, change nothing."

"Which indexes haven't been used in 30 days? Show the evidence, and stage the drops as invisible-index changes for my approval."

Because every answer links back to the underlying digests and plans, the workflow is verify, then approve.

What CloudThinker won't do

• Write to your database from the connection credential. The default user is read-only. Remediation requires a separately granted, scoped credential.
• Run DDL without approval. No index creation, no drops, no ALTER TABLE, unless you've granted the write credential and Auto Mode lets that statement run.
• Kill queries or change server config silently. Same approval gates, same audit trail.
• Make schema-design decisions for you. It will show you that the counters table is a contention hot spot. Whether to shard it, queue it or move it to Redis is an engineering call, and it stays yours.

From two-week backlog to same-day finding

What a team without a full-time DBA can't sustain is the cadence: reading digests weekly, re-checking plans after every deploy, keeping the slow log honest. That's an automation problem, not an effort problem. Teams that move from reactive slow-log archaeology to continuous, approval-gated query management typically cut their worst p95 endpoints by 50 to 90% in the first month, mostly from a handful of well-evidenced indexes.

Try CloudThinker free with 100 premium credits and no card required, connect MySQL with the read-only guide, and see your own first findings table before the next deploy ships a regression.

Related reading: 5 root causes of MySQL slow queries, triage with native tools only, and Auto Mode.