How I Reduced a MySQL Slow Query from 3 seconds to 10 milliseconds

Problem Background

The business reports a query page that takes 3‑5 seconds to load, with a 30% timeout rate. Developers claim there are indexes, the DBA says the execution plan looks fine, and ops say CPU and memory are sufficient. The real issue lies in the query itself.

Environment and Table Schema

We use three tables in an e‑commerce order system:

CREATE TABLE `t_order` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '订单ID',
  `order_no` varchar(32) NOT NULL COMMENT '订单编号',
  `user_id` bigint NOT NULL COMMENT '用户ID',
  `status` tinyint NOT NULL DEFAULT '1' COMMENT '订单状态：1待支付 2已支付 3已发货 4已完成 5已取消',
  `total_amount` decimal(10,2) NOT NULL COMMENT '订单总金额',
  `pay_amount` decimal(10,2) NOT NULL COMMENT '实付金额',
  `pay_time` datetime DEFAULT NULL COMMENT '支付时间',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_order_no` (`order_no`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_user_status` (`user_id`,`status`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单主表';

CREATE TABLE `t_order_item` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '明细ID',
  `order_id` bigint NOT NULL COMMENT '订单ID',
  `user_id` bigint NOT NULL COMMENT '用户ID',
  `goods_name` varchar(128) NOT NULL COMMENT '商品名称',
  `price` decimal(10,2) NOT NULL COMMENT '商品单价',
  `quantity` int NOT NULL COMMENT '购买数量',
  `subtotal` decimal(10,2) NOT NULL COMMENT '小计金额',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_order_id` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单商品明细表';

CREATE TABLE `t_pay_record` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '支付记录ID',
  `order_id` bigint NOT NULL COMMENT '订单ID',
  `user_id` bigint NOT NULL COMMENT '用户ID',
  `pay_no` varchar(64) NOT NULL COMMENT '支付流水号',
  `pay_channel` varchar(16) NOT NULL COMMENT '支付渠道：alipay wechat bankcard',
  `pay_amount` decimal(10,2) NOT NULL COMMENT '支付金额',
  `status` tinyint NOT NULL DEFAULT '1' COMMENT '支付状态：1待支付 2已支付 3已退款',
  `pay_time` datetime DEFAULT NULL COMMENT '支付时间',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_pay_no` (`pay_no`),
  KEY `idx_order_id` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='支付记录表';

Initial Slow Query

SELECT o.id AS order_id, o.order_no, o.status, o.total_amount, o.pay_amount, o.pay_time, o.create_time,
       oi.id AS item_id, oi.goods_name, oi.price, oi.quantity, oi.subtotal,
       pr.pay_no, pr.pay_channel, pr.pay_amount AS real_pay_amount
FROM   t_order o
LEFT   JOIN t_order_item oi ON o.id = oi.order_id
LEFT   JOIN t_pay_record pr ON o.id = pr.order_id
WHERE  o.user_id = 123456
  AND  o.create_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)
ORDER  BY o.create_time DESC
LIMIT  20;

Running on MySQL 5.7.30 it takes 3.2 seconds.

Step 1 – Enable Slow‑Query Log and Collect Samples

# Check if slow query log is on
SHOW VARIABLES LIKE 'slow_query_log';
# Set threshold to 1 second
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
# Use mysqldumpslow to get top queries
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

Step 2 – Analyze the Execution Plan

EXPLAIN shows all three tables using ref access, but the idx_user_id index only covers user_id. The ORDER BY create_time forces a filesort, causing the 15 k rows to be scanned and sorted.

+----+-------------+-------+------------+------+------------------+---------+-------+-------+----------+--------------------------+
| id | select_type | table | partitions | type | key              | key_len | ref   | rows  | filtered | Extra                    |
+----+-------------+-------+------------+------+------------------+---------+-------+-------+----------+--------------------------+
|  1 | SIMPLE      | o     | NULL       | ref  | idx_user_id      | 8       | const | 15680 |   10.00 | Using index condition; Using filesort |
|  1 | SIMPLE      | oi    | NULL       | ref  | idx_order_id     | 8       | o.id  |     4 |  100.00 | NULL                     |
|  1 | SIMPLE      | pr    | NULL       | ref  | idx_order_id     | 8       | o.id  |     1 |  100.00 | NULL                     |
+----+-------------+-------+------------+------+------------------+---------+-------+-------+----------+--------------------------+

Step 3 – Index Optimization

The goal is to create a covering index that includes the WHERE columns ( user_id, create_time) and the ORDER BY column ( create_time) so MySQL can retrieve rows in order without filesort.

Option 1 – Full Covering Index (many columns)

ALTER TABLE t_order ADD INDEX idx_user_time (
  user_id,
  create_time DESC,
  status,
  id,
  order_no,
  total_amount,
  pay_amount,
  pay_time
);

Too large and costly to maintain.

Option 2 – Minimal Covering Index

ALTER TABLE t_order ADD INDEX idx_covering (user_id, create_time DESC);

After adding the index, EXPLAIN shows:

+----+-------------+-------+------------+------+------------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | key        | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+-------+------------+------+------------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | o     | NULL       | ref  | idx_covering| 8      | const |  150 |   10.00 | Using where; Using index |
+----+-------------+-------+------------+------+------------+---------+-------+------+----------+--------------------------+

Rows examined drop from 15 680 to 150, filesort disappears, and the query runs in 8‑12 ms.

Option 3 – Composite Indexes for Joined Tables

ALTER TABLE t_order_item ADD INDEX idx_order_id_covering (
  order_id, user_id, goods_name, price, quantity, subtotal
);
ALTER TABLE t_pay_record ADD INDEX idx_order_id_covering (
  order_id, user_id, pay_no, pay_channel, pay_amount
);

Step 4 – SQL Rewrite

Even with the new indexes, the original LEFT JOIN on t_pay_record pulls rows for unpaid orders. Replace it with an INNER JOIN and filter pr.status = 2 (paid).

SELECT o.id AS order_id, o.order_no, o.status, o.total_amount, o.pay_amount,
       o.pay_time, o.create_time,
       oi.id AS item_id, oi.goods_name, oi.price, oi.quantity, oi.subtotal,
       pr.pay_no, pr.pay_channel, pr.pay_amount AS real_pay_amount
FROM   t_order o
INNER  JOIN t_order_item oi ON o.id = oi.order_id
LEFT   JOIN t_pay_record pr ON o.id = pr.order_id AND pr.status = 2
WHERE  o.user_id = 123456
  AND  o.create_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)
  AND  o.status != 5   -- exclude cancelled orders
ORDER  BY o.create_time DESC
LIMIT  20;

Step 5 – Configuration Tuning

innodb_buffer_pool_size : set to 60‑80 % of RAM (e.g., 48 GB on a 64 GB server) and use 4‑8 instances to reduce lock contention.

sort_buffer_size : increase to 1‑4 MB per connection to reduce temporary files for filesort (if any).

read_rnd_buffer_size : keep at 2‑4 MB.

Step 6 – Full Optimized SQL and Index Summary

SELECT o.id AS order_id, o.order_no, o.status, o.total_amount, o.pay_amount,
       o.pay_time, o.create_time,
       oi.id AS item_id, oi.goods_name, oi.price, oi.quantity, oi.subtotal,
       pr.pay_no, pr.pay_channel, pr.pay_amount AS real_pay_amount
FROM   t_order o
INNER  JOIN t_order_item oi ON o.id = oi.order_id
LEFT   JOIN t_pay_record pr ON o.id = pr.order_id AND pr.status = 2
WHERE  o.user_id = 123456
  AND  o.create_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)
  AND  o.status != 5
ORDER  BY o.create_time DESC
LIMIT  20;

New indexes created:

ALTER TABLE t_order ADD INDEX idx_user_time_covering (user_id, create_time DESC, status, id, order_no, total_amount, pay_amount, pay_time);
ALTER TABLE t_order_item ADD INDEX idx_order_id_covering (order_id, user_id, goods_name, price, quantity, subtotal);
ALTER TABLE t_pay_record ADD INDEX idx_order_id_covering (order_id, status, pay_no, pay_channel, pay_amount);

Step 7 – Deployment and Rollback

Before applying changes in production:

Backup tables with mysqldump.

Test the index creation and query on a staging environment.

Use online DDL: ALTER TABLE … ADD INDEX … ALGORITHM=INPLACE, LOCK=NONE or pt-online-schema-change for zero‑downtime.

Verify the plan with EXPLAIN and measure latency with SET profiling=1 or EXPLAIN ANALYZE (MySQL 8.0+).

If anything goes wrong, drop the new indexes or restore from the backup.

Common Slow‑Query Patterns

SELECT * – replace with needed columns to enable covering indexes.

Implicit type conversion – ensure literals match column types.

OR conditions – split into UNION or add separate indexes.

Large IN lists – batch them.

COUNT(*) on big tables – maintain a counter table or use EXPLAIN row estimate.

JOIN order – use STRAIGHT_JOIN when optimizer picks a bad order.

GROUP BY without index – add index on grouping columns.

MySQL 8.0 Advanced Features

Invisible indexes – test index impact without dropping it.

Histogram statistics – improve cardinality estimates for non‑indexed columns.

Window functions – replace sub‑queries for ranking.

Common Table Expressions (CTE) – simplify complex joins.

EXPLAIN FORMAT=JSON – see detailed cost breakdown.

optimizer_trace – understand why the optimizer chose a plan.

InnoDB Buffer Pool Deep‑Tuning

Check usage with SHOW ENGINE INNODB STATUS and SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%'. Aim for >95 % hit rate. If low, increase innodb_buffer_pool_size and consider multiple instances ( innodb_buffer_pool_instances).

Pre‑warm the pool after a restart using innodb_buffer_pool_dump_at_shutdown / innodb_buffer_pool_load_at_startup or run hot‑query workloads manually.

Production Checklist

# Verify running queries
SHOW PROCESSLIST;
# Check table size and existing indexes
SHOW TABLE STATUS LIKE 't_order';
SHOW INDEX FROM t_order;
# Ensure no long‑running transactions
SELECT * FROM information_schema.INNODB_TRX;
# Backup
mysqldump -h 192.168.1.100 -u root -p --single-transaction --quick \
  --databases yourdb --tables t_order t_order_item t_pay_record \
  > /backup/before_optimize_$(date +%Y%m%d).sql;
# Test on staging before production

Conclusion

The key to MySQL slow‑query optimization is threefold:

Understand exactly what the SQL does via EXPLAIN, profiling, and slow‑query logs.

Design indexes that match the WHERE, ORDER BY and LIMIT clauses, preferably as covering indexes.

Rewrite the SQL to eliminate unnecessary joins, avoid SELECT *, and use proper predicates.

Parameter tuning (buffer pool, sort buffer, etc.) only fine‑tunes performance after the logical problems are solved. Leveraging MySQL 8.0 features such as invisible indexes, histograms, window functions, and JSON EXPLAIN makes the optimization process more precise and safer.

Always verify the plan and benchmark in a test environment, prepare backups and rollback scripts, and apply changes during low‑traffic windows.