How to Slash MySQL Slow Queries from 800ms to 8ms: A Step‑by‑Step Optimization Guide

🔥 Real Impact of Slow Queries

Slow queries can cause response delays, exhaust connection pools, queue other queries, and even bring the whole site down.

🎯 Step 1: Precisely Locate Slow Queries

1.1 Enable Slow Query Log

-- dynamic enable without restart
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 >1s
SET GLOBAL log_queries_not_using_indexes = 'ON';

⚠️ Ops reminder: the slow‑query log adds I/O. Set a reasonable long_query_time (usually 1‑2 s), rotate logs regularly, and optionally limit logging frequency with log_slow_rate_limit.

Configure appropriate long_query_time.

Rotate log files to avoid disk‑space issues.

Use log_slow_rate_limit to control record frequency.

1.2 Use mysqldumpslow for quick analysis

# top 10 by query time
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# top 10 by count
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log
# average query time
mysqldumpslow -s at -t 10 /var/log/mysql/slow.log

1.3 Real‑time monitoring (recommended tools)

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 and tricks

EXPLAIN SELECT * FROM orders WHERE user_id = 12345;
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id = 12345;
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 one.

rows : >10000 – low selectivity, redesign index.

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

2.3 Complex query optimization case

Original query (1.2 s):

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 all tables type=ALL, no indexes, massive row estimates.

All tables full scan.

No suitable index.

Using filesort.

Estimated rows = 5e4 × 1e4 × 8e4 × 5e3 (astronomical).

⚡ Step 3: Index Optimization Strategies

3.1 Single‑column indexes

-- columns 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);
-- foreign keys
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 Composite index design principles

Selectivity principle : place high‑selectivity columns first.

Query frequency principle : put frequently filtered columns first.

Order‑by principle : include ORDER BY columns in the index.

-- 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 row look‑ups
CREATE INDEX idx_orders_cover ON orders(user_id, created_at, id, order_no);

3.3 Post‑optimization query performance

Re‑run EXPLAIN after adding indexes:

+----+-------------+-------+-------+---------------------------+---------------------+---------+---------------+------+-----------------------+
| id | select_type | table | type  | possible_keys             | key                 | key_len | ref           | rows | Extra                 |
+----+-------------+-------+-------+---------------------------+---------------------+---------+---------------+------+-----------------------+
|  1 | SIMPLE      | o     | range | idx_orders_date_user      | idx_orders_date_user| 8       | NULL          |  100 | Using where           |
|  1 | SIMPLE      | u     | eq_ref| PRIMARY,idx_users_city_id | PRIMARY             | 4       | o.user_id     |    1 | Using where           |
|  1 | SIMPLE      | oi    | ref   | idx_order_items_order_id  | idx_order_items_order_id| 4 | o.id          |    2 |                       |
|  1 | SIMPLE      | p     | eq_ref| PRIMARY,idx_products_cat_name| idx_products_cat_name| 8 | oi.product_id |    1 | Using where           |
+----+-------------+-------+-------+---------------------------+---------------------+---------+---------------+------+-----------------------+

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

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

CPU usage: 95 % → 5 %.

🛠️ Step 4: Advanced Optimization Techniques

4.1 Partitioned tables for massive data

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),
    /* … more partitions … */
    PARTITION p202412 VALUES LESS THAN (202501)
);

4.2 Query rewrite example

Inefficient version:

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

Optimized version:

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

4.3 Index maintenance best practice

-- 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 System

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 (bash)

#!/bin/bash
# mysql_slow_monitor.sh
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 Sustainable Optimization Mechanism

Daily Ops Checklist

Analyze slow‑query log weekly.

Monitor index usage.

Review table partition strategy.

Evaluate query‑cache effectiveness.

Refresh table statistics.

Emergency Response Flow

Detect slow query → immediate EXPLAIN.

Assess impact scope → business risk evaluation.

Quick fix → add index or rewrite query.

Validate → monitor key metrics.

Post‑mortem → improve monitoring and alerts.

By following this methodology, operations engineers can turn catastrophic slow‑query incidents into measurable performance gains, lower resource consumption, and higher system stability.