Dashboard Is This Month's Budget Exceeded? > MySQL Index Deep-Dive

A 3-table JOIN query, a confusing EXPLAIN output, and a missed composite index — from EXPLAIN analysis to B+Tree internals to real-world fix.

Table of Contents

1. Query and Objective
2. EXPLAIN Analysis — 3 Tables
3. Root Cause: Why Transactions Are Over-scanned
4. B+Tree Internals — From Root to Leaf
5. Why We Need occurred_month (Not createdAt)
6. Fix: Add idx_tx_hh_cat_month
7. Leftmost Prefix — The Most Important Rule
8. Results After Fix

---

1. Query và mục tiêu

Dashboard hiển thị tất cả budget tháng hiện tại, tính tổng chi tiêu thực tế, phần trăm sử dụng, và đánh dấu khi nào vượt ngưỡng cảnh báo. Query JOIN 3 bảng: budgets → categories → transactions.

SELECT
  c.id          AS category_id,
  c.name        AS category_name,
  b.amount      AS budget_amount,

  COALESCE(SUM(t.amount), 0)                               AS spent_amount,
  b.amount - COALESCE(SUM(t.amount), 0)                    AS remaining_amount,
  ROUND(COALESCE(SUM(t.amount), 0) / NULLIF(b.amount,0) * 100, 2) AS usage_percent,

  b.alert_threshold,
  CASE WHEN ... >= b.alert_threshold THEN 1 ELSE 0 END AS is_over_threshold,
  CASE WHEN ... > b.amount           THEN 1 ELSE 0 END AS is_over_budget,
  COUNT(t.id)                                            AS transaction_count,
  MAX(t.occurred_at)                                     AS last_transaction_at

FROM budgets b
JOIN categories c
  ON  c.id           = b.category_id
  AND c.household_id = b.household_id
  AND c.archived_at IS NULL

LEFT JOIN transactions t
  ON  t.household_id   = b.household_id
  AND t.category_id    = b.category_id
  AND t.type           = 'EXPENSE'
  AND t.deleted_at    IS NULL
  AND t.occurred_month = b.period_month   -- generated column

WHERE  b.household_id = 1
  AND  b.period_month  = '2026-06-01'

GROUP BY b.id, c.id, c.name, b.amount, b.alert_threshold
ORDER BY is_over_budget DESC, usage_percent DESC, c.name ASC;

---

2. Phân tích EXPLAIN — 3 bảng

MySQL thực hiện query theo thứ tự: budgets → categories → transactions (nested loop join). Mỗi bảng chọn index khác nhau.

table	type	key	key_len	rows	filtered	Extra
b	range	uq_budget_scope	12	10	100	Using index condition
c	eq_ref	uq_categories_id_hh	16	1	100	Using where
t	ref	fk_tx_category ⚠️	16	8	100.00	Using where

budgets — chọn uq_budget_scope

Filter: household_id = 1 AND period_month = '2026-06-01'.

Index uq_budget_scope có structure (household_id, category_id, period_month). MySQL chỉ seek được theo household_id vì category_id nằm giữa mà query không filter. period_month được check bằng Using index condition — lọc ngay trên index mà không cần fetch row.

Tại sao không chọn idx_budget_month? idx_budget_month (household_id, period_month) hợp về filtering hơn. Nhưng optimizer chọn uq_budget_scope vì index này có sẵn category_id — giúp join tiếp sang categories và transactions mà không cần fetch thêm từ table. MySQL cost estimate toàn query, không chỉ riêng WHERE.

categories — chọn uq_categories_id_hh

Join condition: c.id = b.category_id AND c.household_id = b.household_id.

Index uq_categories_id_hh (id, household_id) là composite unique anchor — MySQL lookup đúng 1 row bằng 2 equality conditions. Type = eq_ref, rows = 1. Tối ưu nhất có thể.

transactions — chọn fk_tx_category ← vấn đề ở đây

fk_tx_category là index trên (category_id, household_id) — chỉ 2 cột. MySQL lookup bằng 2 equality conditions, nhưng các filter còn lại (type='EXPENSE', deleted_at IS NULL, occurred_month='2026-06-01') phải check ở server layer — đó là Using where.

---

3. Root cause: tại sao transactions bị scan thừa

Có 7 candidate indexes cho bảng transactions. Tại sao MySQL chọn fk_tx_category mà không chọn idx_tx_hh_cat_time?

idx_tx_hh_cat_time (bị bỏ qua):       fk_tx_category (được chọn):
(household_id,                         (category_id,
 category_id,                           household_id)
 occurred_at)   ← cột 3
                                       → Chỉ 2 cột, nhỏ hơn
JOIN condition dùng:                   → Cùng efficiency với
occurred_month  ← khác column!           idx_tx_hh_cat_time
                                         (2 equality lookups)
→ MySQL không thể dùng                 → B+Tree node nhỏ hơn
  cột 3 làm range bound                  → ít I/O hơn
→ chỉ dùng được 2 cột đầu
→ index 3 cột nhưng to hơn
  fk_tx_category → MySQL bỏ qua

Điểm mấu chốt: occurred_month là generated column (DATE_FORMAT(occurred_at, '%Y-%m-01')). MySQL không tự rewrite occurred_month = '2026-06-01' thành range trên occurred_at. Vì vậy idx_tx_hh_cat_time không thể dùng cột thứ 3 — hiệu quả ngang fk_tx_category nhưng index lớn hơn.

Kết quả: mỗi budget row, MySQL đọc ~8 transactions của cả nhiều tháng, rồi mới filter occurred_month ở server. Khi data lớn hơn (hàng năm transactions), con số này tệ hơn rất nhiều.

---

4. B+Tree internals — từ root đến leaf

1 Node = 1 Page = 16KB

InnoDB không đọc từng byte từ disk — OS đọc theo block cố định. InnoDB dùng page 16KB làm đơn vị: mỗi lần đọc = 1 page = 1 disk I/O. Thiết kế 1 node = 1 page để tận dụng tối đa mỗi I/O.

• 16KB = 1 page / 1 node
• ~655 keys / internal node (key 19B + pointer 6B với index này)
• 3–4 tầng là đủ cho 150 triệu rows

Tầng 1 (root):     1 page  × 655 pointers = 655 subtrees
Tầng 2 (internal): 655 pages × 655 pointers = ~430,000 subtrees
Tầng 3 (leaf):     ~430,000 pages × 150 rows = ~64,000,000 rows

→ 64 triệu rows chỉ cần 3 disk reads

N keys → N+1 pointers. 3 cây cọc dựng 4 khoảng — N keys tạo N+1 khoảng phân vùng. Root 655 keys → 656 pointers → 656 subtrees.

Composite key: sort theo thứ tự từ trái sang phải

Index idx_tx_hh_cat_month ghép 3 cột thành 1 key duy nhất, sort theo thứ tự ưu tiên: hh_id → cat_id → occurred_month.

-- Thứ tự trong B+Tree leaf (đã sort):
(1, 5, '2026-04-01') → r01   ┐
(1, 5, '2026-05-01') → r02   │  hh=1, cat=5
(1, 5, '2026-06-01') → r03   │  ← target
(1, 5, '2026-06-01') → r04   ┘
(1, 7, '2026-05-01') → r05   ┐  hh=1, cat=7
(1, 7, '2026-06-01') → r06   ┘
─────────────────────────────── ranh giới hh_id
(2, 3, '2026-05-01') → r07   ┐
(2, 5, '2026-06-01') → r08   │  hh=2 — hoàn toàn tách biệt
(2, 9, '2026-07-01') → r09   ┘

Cây B+Tree

                    ┌────────────────────────────────────┐
                    │            ROOT NODE               │
                    │   [ (1,5,05-01)    (1,7,05-01) ]   │
                    └────────────┬────────────┬──────────┘
               ┌─────────────────┘            └──────────────────┐
               ▼                                                  ▼
   ┌────────────────────┐                          ┌──────────────────────┐
   │  INTERNAL NODE 1   │                          │   INTERNAL NODE 2    │
   │  [ (1,5,05-01) ]   │                          │  [ (2,5,05-01) ]     │
   └──────┬─────────────┘                          └────────┬─────────────┘
     ┌────┘      └────────────┐               ┌────────────┘      └────┐
     ▼                        ▼               ▼                        ▼
┌──────────┐          ┌──────────┐    ┌──────────┐            ┌──────────┐
│  LEAF 1  │─────────▶│  LEAF 2  │───▶│  LEAF 3  │───────────▶│  LEAF 4  │
├──────────┤          ├──────────┤    ├──────────┤            ├──────────┤
│(1,5,04)  │          │(1,5,06)  │    │(1,7,05)  │            │(2,5,06)  │
│  → r01   │          │  → r03   │    │  → r05   │            │  → r08   │
│(1,5,05)  │          │(1,5,06)  │    │(1,7,06)  │            │(2,5,06)  │
│  → r02   │          │  → r04   │    │  → r06   │            │  → r09   │
└──────────┘          └──────────┘    └──────────┘            └──────────┘

Chỉ leaf nodes chứa row pointer thực sự. Internal nodes và root chỉ chứa separator keys để định hướng. Leaf nodes được nối bằng linked list để scan liên tiếp không cần quay lại root.

Binary search với short-circuit từ trái sang phải

MySQL so sánh composite key theo thứ tự ưu tiên — khi field đầu tiên đã khác nhau, dừng ngay, không xét field tiếp theo.

-- Query: (hh=2, cat=5, month='2026-06-01')
-- Root 500 keys, iteration 1: mid = (hh=2, cat=3, month='2026-05-01')

target.hh (2) == mid.hh (2)  → chưa kết luận, xét tiếp
target.cat (5) > mid.cat (3)  → STOP — tìm nửa phải
                                 month không cần xét

-- Iteration 2: mid = (hh=2, cat=7, month='2026-06-01')
target.hh (2) == mid.hh (2)  → xét tiếp
target.cat (5) <  mid.cat (7)  → STOP — tìm nửa trái

-- Iteration 3: mid = (hh=2, cat=5, month='2026-05-01')
target.hh (2) == mid.hh (2)  → xét tiếp
target.cat (5) == mid.cat (5)  → xét tiếp
target.month ('06') > mid.month ('05') → STOP — tìm nửa phải

-- Tổng: log2(500) ≈ 9 iterations, mỗi iteration 1–3 compares

Leaf node: binary search để vào đúng điểm, linked list để collect

-- Sau khi xuống đúng leaf page:
Binary search tìm entry đầu tiên match (2,5,06) → r08

-- Scan forward qua linked list:
(2,5,'2026-06-01') → r08  ✓ HIT
(2,5,'2026-06-01') → r09  ✓ HIT
(2,5,'2026-07-01') → r10  ✗ month tăng → STOP ngay

-- Không cần quay lại root để tìm tiếp!

Tổng: 3 disk reads (root → internal → leaf) để lấy đúng rows cần.

---

5. Tại sao cần occurred_month (không phải createdAt)

created_at    = khi nào record được INSERT vào database
occurred_at   = khi nào giao dịch thực sự xảy ra ngoài đời thực
occurred_month = DATE_FORMAT(occurred_at, '%Y-%m-01')  ← normalized để index

Dùng created_at sẽ phá vỡ budget tracking trong các trường hợp phổ biến:

Nhập liệu trễ: Mua xăng ngày 31/05, nhập app ngày 03/06.

• created_at = 2026-06-03 → tính vào budget tháng 6. Sai.
• occurred_at = 2026-05-31 → tính vào budget tháng 5. Đúng.

Import sao kê ngân hàng (source = IMPORT / OCR): Import 50 giao dịch từ tháng 1–3.

• created_at = 2026-07-13 (hôm nay) → tất cả dồn vào budget tháng 7. Vô nghĩa.
• occurred_at rải từ tháng 1–3 → đúng từng tháng. Đúng.

Recurring transactions (source = RECURRING): Job generate tiền thuê tháng 5 chạy lúc 00:01 ngày 01/06.

• created_at = 2026-06-01 → budget tháng 6. Sai.
• occurred_at = 2026-05-01 → budget tháng 5. Đúng.

occurred_month là stored generated column — MySQL tự tính và lưu khi INSERT/UPDATE. Nó có thể được index như cột bình thường, cho phép equality lookup thay vì range scan (xem phần tiếp).

---

6. Fix: thêm idx_tx_hh_cat_month

// Drizzle ORM schema (packages/db/src/schema/money.ts)
index('idx_tx_hh_cat_month').on(
  table.householdId, // eq: const từ WHERE
  table.categoryId, // eq: ref từ budgets row
  table.occurredMonth, // eq: const '2026-06-01'
)

-- Migration được generate (0004_cheerful_punisher.sql):
CREATE INDEX idx_tx_hh_cat_month
  ON transactions (household_id, category_id, occurred_month);

Tại sao index này hoạt động tốt

Budget query JOIN transactions với 3 equality conditions:

• household_id = 1 (const từ WHERE)
• category_id = b.category_id (ref từ budgets row)
• occurred_month = '2026-06-01' (const từ WHERE)

Index mới có đúng 3 cột theo đúng thứ tự — MySQL dùng cả 3 làm equality lookup.

Không có index mới:
  budgets (10 rows) → mỗi row lookup transactions
    → fk_tx_category (hh, cat): tìm ~8 rows của tất cả các tháng
    → server filter: occurred_month, type, deleted_at
    → tổng examined: ~80 rows, nhiều rows là waste

Với idx_tx_hh_cat_month:
  budgets (10 rows) → mỗi row lookup transactions
    → 3 equality: hh + cat + month → tìm đúng ~2-3 rows của tháng đó
    → không cần server filter cho occurred_month
    → tổng examined: ~20-30 rows, gần như không có waste

---

7. Leftmost prefix — quy tắc quan trọng nhất

Index (hh_id, cat_id, occurred_month) chỉ hoạt động hiệu quả khi query dùng cột liên tục từ trái sang phải.

Query condition	Cột dùng được	Hiệu quả	Lý do
hh_id = 1	Cột 1	✓ OK	Prefix hợp lệ
hh_id = 1 AND cat_id = 5	Cột 1 + 2	✓ Tốt hơn	Prefix hợp lệ 2 cột
hh_id = 1 AND cat_id = 5 AND occurred_month = '...'	Cột 1 + 2 + 3	✓ Tối ưu	Đủ 3 cột, equality lookup
cat_id = 5	Không có	✗ Index vô dụng	Bỏ qua cột 1
hh_id = 1 AND occurred_month = '...'	Chỉ cột 1	✗ Kém	Nhảy qua cột 2

Tại sao phải liên tục từ trái? B+Tree sort theo (hh_id, cat_id, occurred_month). Nếu bỏ cat_id, các giá trị occurred_month nằm rải rác khắp nơi trong cây — không có lower/upper bound để binary search.

Đây cũng là lý do household_id phải là cột đầu tiên trong mọi index: nó chia B+Tree thành các vùng hoàn toàn tách biệt theo household, đảm bảo cả performance lẫn data isolation.

---

8. Kết quả sau fix

Metric	Trước (fk_tx_category)	Sau (idx_tx_hh_cat_month)
Key được dùng	fk_tx_category (cat_id, hh_id)	idx_tx_hh_cat_month (hh, cat, month)
Cột index dùng được	2/2 (không có month)	3/3 (bao gồm month)
Rows examined / budget	~8 (tất cả tháng của category)	~2–3 (chỉ tháng đang query)
Extra	Using where (server filter)	Không cần Using where cho month
Scale khi data lớn	Tệ dần theo số tháng	Ổn định, không phụ thuộc số tháng

---

Takeaway: EXPLAIN không chỉ cho biết index nào được dùng — nó cho biết tại sao. rows cao + Using where = index lookup đúng nhưng filter sai chỗ. filtered = 100% + Using where = MySQL không estimate được, cần thêm index để đưa filter vào index layer thay vì server layer.