MySQL Slow Query Diagnose (Diagnosis + Optimization)

Overview

Slow queries are the #1 cause of database performance degradation. This skill goes beyond simply listing slow queries — it performs root cause analysis for each one: how many rows scanned vs returned, which index was used (or missed), and what index to add to fix it.

Key capabilities:

• Identify TOP N slow queries by execution time, lock time, or scan rows
• Calculate index hit rate (rows_examined / rows_sent) — the key metric for query efficiency
• Analyze EXPLAIN plans to identify full table scans, filesort, temporary tables
• Suggest covering indexes with estimated improvement
• Output a prioritized action list sorted by impact

Real-world result: A DBA team reduced average slow query resolution from 20 minutes to 8.3 seconds — 144x improvement — by having AI perform the diagnosis automatically.

When to Use

Apply this skill when:

• Database response time suddenly increases
• Application reports query timeouts
• Monitoring shows slow query count spike
• Periodic performance review / health check
• After schema changes to verify query performance
• Developer asks "why is this query slow?"

Required MCP Server

Required MySQL Privileges

• SELECT on mysql.slow_log or performance_schema.events_statements_summary_by_digest
• PROCESS — for EXPLAIN on other sessions' queries

Diagnostic Workflow

Step 1: Collect Slow Queries

-- Option A: MySQL 5.7+ performance_schema (recommended)
SELECT
  DIGEST_TEXT AS sql_template,
  COUNT_STAR AS exec_count,
  ROUND(SUM_TIMER_WAIT / 1000000000000, 3) AS total_time_sec,
  ROUND(AVG_TIMER_WAIT / 1000000000000, 3) AS avg_time_sec,
  ROUND(MAX_TIMER_WAIT / 1000000000000, 3) AS max_time_sec,
  ROUND(SUM_ROWS_EXAMINED / COUNT_STAR, 0) AS avg_rows_examined,
  ROUND(SUM_ROWS_SENT / COUNT_STAR, 0) AS avg_rows_sent,
  SUM_NO_INDEX_USED AS no_index_count
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT IS NOT NULL
  AND SUM_TIMER_WAIT > 0
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

-- Option B: slow_log table (if enabled)
SELECT
  start_time,
  user_host,
  query_time,
  lock_time,
  rows_sent,
  rows_examined,
  LEFT(sql_text, 300) AS sql_fragment
FROM mysql.slow_log
WHERE start_time > DATE_SUB(NOW(), INTERVAL 1 HOUR)
ORDER BY query_time DESC
LIMIT 20;

Also check overall slow query status:

SHOW GLOBAL STATUS LIKE 'Slow_queries';
SHOW GLOBAL STATUS LIKE 'Slow_launch_threads';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'slow_query_log';

Step 2: Analyze Each Top Query

For each slow query identified, run:

-- Get the EXPLAIN plan
EXPLAIN FORMAT=JSON {the_query};

-- Check current table indexes
SHOW INDEX FROM {table_name};

-- Check table size and row count
SELECT
  TABLE_NAME,
  TABLE_ROWS,
  ROUND(DATA_LENGTH / 1024 / 1024, 2) AS data_mb,
  ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS index_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = '{db}' AND TABLE_NAME = '{table}';

Step 3: Calculate Efficiency Metrics

For each query, compute:

Classification:

IF index_hit_rate < 1% AND no_index_used:
  → Type: MISSING_INDEX (Critical)
  → Action: Add index
ELSE IF scan_efficiency > 50%:
  → Type: FULL_SCAN (High)
  → Action: Optimize query or add covering index
ELSE IF lock_ratio > 30%:
  → Type: LOCK_CONTENTION (Medium)
  → Action: Check concurrent transactions
ELSE IF avg_time > threshold:
  → Type: DATA_VOLUME (Medium)
  → Action: Archive or partition

Step 4: Generate Index Recommendations

For each missing index case, suggest:

-- Example: query is SELECT * FROM orders WHERE status = ? AND create_time > ?
-- Current: full table scan, 12M rows examined, 380 rows returned
-- Index hit rate: 380/12800000 = 0.003% → CRITICAL

-- Recommended index:
ALTER TABLE orders ADD INDEX idx_status_createtime (status, create_time);

-- Expected improvement:
-- rows_examined: 12,000,000 → ~40,000 (300x reduction)
-- query_time: 23.7s → <0.1s

Index design rules:

1. Equality first — columns with = condition go first in composite index
2. Range last — columns with >, <, BETWEEN go last
3. Covering index — if possible, include all SELECT columns to avoid table lookup
4. Order matters — (status, create_time) ≠ (create_time, status)

Output Templates

Initial Report

🔍 MySQL Slow Query Diagnostic Report
══════════════════════════════════════
📋 Instance: {host}:{port}
   Analysis Period: Last {hours} hours
   Slow Query Threshold: {threshold}s
   Total Slow Queries: {count}

🔴 TOP {N} Slow Queries (by total impact)
─────────────────────────────────────────

  TOP 1 ⚡ Total Impact: {total_time}s across {count} executions
  ┌─────────────────────────────────────────────┐
  │ SQL: {sql_template}                         │
  │ Avg Time: {avg}s  |  Max: {max}s            │
  │ Rows Examined: {examined}  |  Returned: {sent}│
  │ Index Hit Rate: {rate}% 🔴 MISSING INDEX     │
  │                                             │
  │ Root Cause: Full table scan on {table}       │
  │   → No index on ({columns})                 │
  │                                             │
  │ ✅ Recommended:                              │
  │   ALTER TABLE {table} ADD INDEX              │
  │     idx_{name} ({columns});                  │
  │   Expected: {current}s → <0.1s              │
  └─────────────────────────────────────────────┘

  TOP 2 ⚡ ...
  TOP 3 ⚡ ...

📊 Summary
  | Rank | SQL Pattern | Executions | Avg Time | Hit Rate | Fix |
  |------|------------|------------|----------|----------|-----|
  | 1    | {pattern}  | {count}    | {time}s  | {rate}%  | Add index |
  | 2    | ...        | ...        | ...      | ...      | ... |

⚡ Priority Actions
  1. [Critical] Add index on {table}({cols}) — fixes TOP 1, est. 300x speedup
  2. [High] Add index on {table}({cols}) — fixes TOP 2
  3. [Medium] Schedule batch query for off-peak — TOP 3 is legitimate report

After Index Applied (Follow-up)

✅ Index Verification
══════════════════
  Query: {sql_template}
  Before: avg {old}s, examined {old_rows} rows
  After:  avg {new}s, examined {new_rows} rows
  Improvement: {factor}x faster 🎉

Example Usage

User: "Check slow queries on db-prod-01, last 1 hour"

AI Response:

I'll analyze slow queries from the last hour.

🔍 MySQL Slow Query Diagnostic Report
══════════════════════════════════════
📋 Instance: db-prod-01:3306
   Analysis Period: Last 1 hour
   Total Slow Queries: 62

🔴 TOP 3 Slow Queries

  TOP 1 ⚡ 23.7s avg × 47 executions = 1,114s total impact
  ┌────────────────────────────────────────────────┐
  │ SELECT * FROM order_detail                     │
  │   WHERE create_time > ? AND status = ?         │
  │                                                │
  │ Avg Time: 23.7s | Max: 45.2s                  │
  │ Rows Examined: 12,800,000 | Returned: 380      │
  │ Index Hit Rate: 0.003% 🔴                       │
  │                                                │
  │ Root Cause: Full table scan — no index on      │
  │   (status, create_time)                        │
  │                                                │
  │ ✅ Fix: ALTER TABLE order_detail ADD INDEX      │
  │      idx_status_createtime (status, create_time)│
  │   Expected: 23.7s → <0.1s (237x faster)        │
  └────────────────────────────────────────────────┘

  TOP 2 ⚡ 8.4s avg × 12 executions = 100.8s total impact
  │ Full scan on user_order (5.6M rows)
  │ Fix: ADD INDEX (merchant_id)

  TOP 3 ⚡ 4.2s avg × 3 executions = 12.6s total impact
  │ Index hit, batch report — schedule off-peak

⚡ Priority Actions:
  1. [Critical] Add (status, create_time) on order_detail
  2. [High] Add (merchant_id) on user_order
  3. [Info] Schedule TOP 3 report query to 02:00-05:00

Best Practices

Index Design Checklist

• Equality columns first, range columns last
• High-cardinality columns first in composite index
• Consider covering index for hot queries (avoid table lookup)
• Check existing indexes before adding (avoid duplicates)
• Estimate index size: rows × avg_col_size × 1.5
• Add indexes online (MySQL 5.6+ supports ALTER TABLE ... ALGORITHM=INPLACE)

When NOT to Add an Index

• Query runs infrequently (batch job once a day)
• Table is small (< 10,000 rows) — full scan may be faster
• Write-heavy table — each index adds write overhead
• Query already uses a good index but returns many rows (data volume issue)

Common Anti-Patterns

Quick Reference

Use this skill for intelligent slow query diagnosis — not just listing queries, but explaining WHY they're slow and HOW to fix them.