Tối ưu MySQL query với 300k+ records

Bài toán

Có một bảng orders với ~300k records, query sau mất 12 giây:

SELECT o.id, o.created_at, u.name, u.email,
       COUNT(oi.id) as item_count,
       SUM(oi.price * oi.quantity) as total
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status = 'completed'
  AND o.created_at >= '2026-01-01'
  AND o.created_at < '2026-04-01'
GROUP BY o.id, u.name, u.email
ORDER BY o.created_at DESC
LIMIT 50;

Analyze với EXPLAIN

EXPLAIN SELECT ...;

+----+-------+--------+------+------+---------+--------+---------+
| id | type  | table  | key  | rows | filtered| Extra  |         |
+----+-------+--------+------+------+---------+--------+---------+
|  1 | ALL   | o      | NULL | 298k |   11.11 | Using filesort   |
|  1 | ref   | u      | PRI  |    1 |  100.00 |                  |
|  1 | ref   | oi     | NULL | 3    |  100.00 |                  |
+----+-------+--------+------+------+---------+--------+---------+

Vấn đề: type: ALL trên bảng orders = full table scan 298k rows.

Tạo Composite Index

-- Index bao phủ cả status và created_at (thứ tự quan trọng)
CREATE INDEX idx_orders_status_created
ON orders (status, created_at);

-- Hoặc với covering index nếu query nhiều
CREATE INDEX idx_orders_status_created_user
ON orders (status, created_at, user_id);

Sau khi thêm index:

| type  | key                        | rows | Extra       |
| range | idx_orders_status_created  | 4800 | Using index |

Từ 298k rows xuống còn 4800 rows cần scan.

Rewrite Query

Vẫn còn chậm vì filesort. Thêm subquery để limit trước khi JOIN:

SELECT o.id, o.created_at, u.name, u.email,
       COUNT(oi.id) as item_count,
       SUM(oi.price * oi.quantity) as total
FROM (
  SELECT id, created_at, user_id
  FROM orders
  WHERE status = 'completed'
    AND created_at >= '2026-01-01'
    AND created_at < '2026-04-01'
  ORDER BY created_at DESC
  LIMIT 50
) o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id, u.name, u.email
ORDER BY o.created_at DESC;

Trick này gọi là "late row lookup" — chỉ JOIN 50 rows cần thiết thay vì toàn bộ dataset.

Kết quả

Tổng cải thiện: 60x faster, từ 12s xuống 200ms.