How to Slash MySQL Slow Queries from 0.8 s to 8 ms: A Proven Optimization Playbook

MySQL Slow Query Optimization Guide

Real case: An e‑commerce order‑lookup API improved from an average response time of 800 ms to 8 ms, raising QPS from 200 to over 2000.

As a seasoned operations engineer, I have seen many production outages caused by slow queries. This article shares a battle‑tested methodology for analyzing and indexing MySQL queries to eliminate performance bottlenecks.

🔥 The Real Harm of Slow Queries

Case 1: Avalanche Effect

-- This seemingly harmless query almost crashed the whole system
SELECT *
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE o.created_at >= '2024-01-01'
  AND u.status = 'active'
ORDER BY o.created_at DESC;

Impact analysis:

Execution time: 2.3 seconds

Connection pool exhausted under concurrency

Other normal queries queued

Entire site becomes unavailable

🎯 Step 1: Precisely Locate Slow Queries

1.1 Enable Slow Query Log (Production‑Safe Settings)

-- Enable dynamically without restarting MySQL
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 1;  -- Record queries longer than 1 second
SET GLOBAL log_queries_not_using_indexes = 'ON';

⚠️ Ops Reminder: Slow‑query logging adds I/O overhead. Recommended:

Set a reasonable long_query_time (usually 1‑2 seconds)

Rotate log files regularly to avoid disk‑space exhaustion

Configure log_slow_rate_limit to control logging frequency

1.2 Use mysqldumpslow for Quick Analysis

# Sort by query time, show TOP 10
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# Sort by query count, find frequently executed slow queries
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

# Combined analysis: sort by average query time
mysqldumpslow -s at -t 10 /var/log/mysql/slow.log

1.3 Real‑time Monitoring (Recommended Tools)

-- View currently running slow queries
SELECT id, user, host, db, command, time, state, info
FROM information_schema.processlist
WHERE command != 'Sleep' AND time > 5
ORDER BY time DESC;

🔍 Step 2: Deep Execution‑Plan Analysis

2.1 EXPLAIN Details & Practical Tips

-- Basic EXPLAIN
EXPLAIN SELECT * FROM orders WHERE user_id = 12345;

-- More detailed JSON format (MySQL 8.0+)
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id = 12345;

-- Recommended in MySQL 8.0+
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 12345;

2.2 Key Fields Interpretation (Ops View)

type : ALL – full table scan, must be optimized

possible_keys : NULL – missing index, create immediately

rows : >10 000 – poor selectivity, redesign index

Extra : Using filesort – avoid ORDER BY on non‑indexed columns

2.3 Real‑World Complex Query Optimization

Original query (1.2 seconds):

SELECT o.id, o.order_no, u.username, p.name AS product_name
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.created_at BETWEEN '2024-01-01' AND '2024-01-31'
  AND u.city = 'Shanghai'
  AND p.category_id = 10
ORDER BY o.created_at DESC
LIMIT 20;

EXPLAIN shows full scans on all tables, no usable indexes, and a filesort, leading to astronomical row estimates.

⚡ Step 3: Index Optimization Strategies

3.1 Single‑Column Indexes

-- Index fields frequently used in WHERE
CREATE INDEX idx_orders_created_at ON orders(created_at);
CREATE INDEX idx_users_city ON users(city);
CREATE INDEX idx_products_category ON products(category_id);

-- Index foreign keys to speed up JOINs
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

3.2 The Art of Composite Indexes

Design Principles :

High‑selectivity columns first

Frequently used query conditions first

Include ORDER BY columns when possible

-- Optimized composite indexes
CREATE INDEX idx_orders_date_user ON orders(created_at, user_id);
CREATE INDEX idx_users_city_id ON users(city, id);
CREATE INDEX idx_products_cat_name ON products(category_id, name);

-- Covering index to avoid back‑table lookups
CREATE INDEX idx_orders_cover ON orders(user_id, created_at, id, order_no);

3.3 Optimized Query Performance

Re‑running EXPLAIN after applying indexes shows range scans and dramatically reduced row estimates.

Execution time: 1.2 seconds → 15 ms (≈80× faster)

Scanned rows: >4 billion → 204 rows (99.999995 % reduction)

CPU usage: 95 % → 5 %

🛠️ Step 4: Advanced Optimization Techniques

4.1 Partitioned Tables

-- Monthly partitioned orders table
CREATE TABLE orders_partitioned (
  id BIGINT NOT NULL AUTO_INCREMENT,
  user_id INT NOT NULL,
  order_no VARCHAR(50) NOT NULL,
  created_at DATETIME NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (id, created_at),
  INDEX idx_user_date (user_id, created_at)
) PARTITION BY RANGE (YEAR(created_at)*100 + MONTH(created_at)) (
  PARTITION p202401 VALUES LESS THAN (202402),
  PARTITION p202402 VALUES LESS THAN (202403),
  PARTITION p202403 VALUES LESS THAN (202404),
  ...
  PARTITION p202412 VALUES LESS THAN (202501)
);

4.2 Query Rewriting

Original (inefficient):

SELECT * FROM orders WHERE user_id IN (
  SELECT id FROM users WHERE city = 'Shanghai'
);

Optimized (efficient):

SELECT o.*
FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE u.city = 'Shanghai';

4.3 Index Maintenance Best Practices

-- Periodically analyze index usage
SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, STAT_VALUE AS pages_used
FROM information_schema.INNODB_SYS_TABLESTATS;

-- Find unused indexes
SELECT t.TABLE_SCHEMA, t.TABLE_NAME, t.INDEX_NAME
FROM information_schema.statistics t
LEFT JOIN performance_schema.table_io_waits_summary_by_index_usage p
  ON t.TABLE_SCHEMA = p.OBJECT_SCHEMA
 AND t.TABLE_NAME = p.OBJECT_NAME
 AND t.INDEX_NAME = p.INDEX_NAME
WHERE p.INDEX_NAME IS NULL
  AND t.TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema');

📊 Step 5: Monitoring & Alerting

5.1 Key Monitoring Metrics

-- Slow‑query count
SHOW GLOBAL STATUS LIKE 'Slow_queries';

-- Query cache hit rate
SHOW GLOBAL STATUS LIKE 'Qcache%';

-- InnoDB buffer‑pool hit rate
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

5.2 Automated Monitoring Script

#!/bin/bash
# mysql_slow_monitor.sh – simple slow‑query alert
MYSQL_USER="monitor"
MYSQL_PASS="your_password"
SLOW_LOG="/var/log/mysql/slow.log"
ALERT_THRESHOLD=10

SLOW_COUNT=$(mysqldumpslow -t 999999 $SLOW_LOG | grep "Time:" | wc -l)

if [ $SLOW_COUNT -gt $ALERT_THRESHOLD ]; then
  echo "ALERT: Detected $SLOW_COUNT slow queries, exceeding threshold $ALERT_THRESHOLD"
  # Integrate with DingTalk, email, etc.
fi

🎯 Summary: Build a Long‑Term Optimization Mechanism

Daily Ops Checklist

Analyze slow‑query logs weekly

Monitor index usage regularly

Review table partitioning strategy

Evaluate query‑cache effectiveness

Update table statistics

Emergency Response Flow

Detect slow query → immediate EXPLAIN analysis

Assess impact scope → evaluate business risk

Quick fix → add index or rewrite query

Validate effect → monitor key metrics

Post‑mortem → improve monitoring & alerts