Moving Computation from PHP to the Browser: 12 Seconds to 12 ms

I took on a job fixing a system that matched thousands of items. Users entered a set of conditions, and the system compared them against 1,090 available items, along with 5 years of history for each item, then calculated a ranking by likelihood. The problem was that the results page took 12 seconds or more to load. Changing a filter meant waiting another 12 seconds. I rebuilt the architecture of the entire page, moving the formulas out of PHP and into the browser, and ended up at 12 milliseconds.

The filenames, table names, and field names in this article are examples I made up. The approach and code structure are what I actually use in production.

The cause: formulas written to work one row at a time

The original code was not wrong. It was even easy to read. It looped through items one at a time, and for each item it asked the database about last year's history, the year before that, the number of places available, the number of applications, and the minimum and maximum values. Each question was a separate query.

// includes/matcher.php — โครงเดิม อ่านง่าย แต่ยิง SQL ทีละแถว
foreach ($items as $item) {                       // 1,090 รอบ
    foreach ([2564, 2565, 2566, 2567, 2568] as $y) {  // 5 ปี
        // subquery ชุดนี้ยิงต่อ "รายการ x ปี" = 5 ครั้งต่อรายการเป็นอย่างน้อย
        $h = $db->query("
            SELECT quota, applied, passed, min_score, max_score
            FROM   item_history
            WHERE  item_code = ?
              AND  year = ?
              AND  variant_key LIKE ?      -- ตรงนี้แหละตัวปัญหา
            ORDER BY id LIMIT 1", [$item['code'], $y, '%'.$item['pattern'].'%']);
        $item['history'][$y] = $h->fetch();
    }
    $item['buffer'] = buffer_of($item);           // สูตรอยู่ฝั่ง PHP
    $item['chance'] = chance_of($item);
}

When I counted, each item took between 10 and 25 statements, depending on how many years of history and how many variants it had. Multiplied by 1,090 items, that came to roughly 10,900 to 27,250 SQL statements per page load. Each statement was very fast, taking a fraction of a millisecond. The problem was not a slow statement. It was purely the number of calls. The round-trip and parsing overhead of each statement, added up over tens of thousands of calls, became 12 seconds.

What made me decide to rebuild the entire page was not just those 12 seconds. It was that users had to wait another 12 seconds after changing a filter, even though the dataset was exactly the same. Nothing had changed except the filter conditions. The same work was being recalculated ten times for ten different answers.

Layer one: precompute a summary table

The heaviest work in the loop above was matching an "item" to a "variant" in each year's history, because variant names were not written consistently from year to year. Some years used a long name, some used an abbreviation, and some changed the word order. I moved all of this work out of the request path and into a materialized table, computed once in advance.

-- ตารางสรุป: หนึ่งแถว = หนึ่งรายการ x หนึ่งปี ชี้ไปที่แถวประวัติที่จับคู่ได้แล้ว
CREATE TABLE item_history_matched (
  item_code    VARCHAR(32)  NOT NULL,
  year         SMALLINT     NOT NULL,
  history_id   INT UNSIGNED NULL,      -- NULL = จับคู่ไม่ได้ (ตั้งใจให้เป็น NULL)
  variant_key  VARCHAR(255) NULL,
  match_score  INT          NOT NULL DEFAULT 0,
  quota        SMALLINT     NULL,
  applied      INT          NULL,
  passed       INT          NULL,
  min_score    DECIMAL(7,3) NULL,
  max_score    DECIMAL(7,3) NULL,
  PRIMARY KEY (item_code, year),
  KEY idx_year (year)
) ENGINE=InnoDB;

My first precompute implementation was pure SQL, using LIKE and a window function to find the best match per group. It worked, but took around 90 seconds per rebuild. Worse, it produced incorrect matches when variant names were unusually long, because LIKE does not understand word structure. I discarded that SQL version and rewrote it in Python, tokenizing the names and scoring the matches myself.

# tools/precompute_matched.py — สร้างตารางสรุป (rebuild เต็ม ~1 วินาที)
import re

NOISE = {"หลักสูตร", "กลุ่ม", "ประเภท", "เกณฑ์", "รูปแบบ", "แบบ"}
SPLIT = re.compile(r"[\s|()/,.\-]+")

def tokens(s: str) -> set:
    out = set()
    for t in SPLIT.split(s or ""):
        t = t.strip()
        if not t or t in NOISE:
            continue
        out.add(t)          # เก็บ token ตัวเลขหลักเดียวไว้ด้วย สำคัญมากกับ "รูปแบบที่ 3"
    return out

Tokenizing and comparing sets was vastly faster than LIKE. A full table rebuild dropped from 90 seconds to about 1 second. More important than the speed, though, was that I could question the result. I could see why a particular row had been matched to a particular candidate.

Matching rules: reject first, then score

At first, I used scoring alone: score every candidate and pick the highest. The results "looked right" but were actually wrong, because a candidate of a completely different type could happen to share enough words to beat a candidate of the correct type with a shorter name. I split this into two layers. The first was a hard reject, removing candidates from consideration entirely instead of just deducting points.

def hard_reject(item, cand) -> bool:
    """คืน True = ตัดทิ้ง ไม่ให้เข้ารอบให้คะแนน"""
    # 1) กลุ่มสิทธิ์ไม่ตรง — ฝั่งหนึ่งระบุกลุ่มพิเศษ อีกฝั่งไม่ระบุ
    if item.special_group != cand.special_group:
        return True
    # 2) ชนิดชุดเกณฑ์ระบุมาทั้งคู่ แต่คนละชนิด (SET-A / SET-B / SET-C)
    if item.set_type and cand.set_type and item.set_type != cand.set_type:
        return True
    # 3) "รูปแบบที่ N" ระบุมาทั้งคู่ แต่ N ไม่ตรง
    if item.pattern_no and cand.pattern_no and item.pattern_no != cand.pattern_no:
        return True
    return False

def soft_score(item, cand) -> int:
    s = 0
    if cand.legacy_code and cand.legacy_code == item.legacy_code:
        s += 10000                      # รหัสเก่าแบบ _vNN ตรงเป๊ะ ชนะทุกอย่าง
    if item.pattern_no and item.pattern_no == cand.pattern_no:
        s += 500
    if item.set_type and item.set_type == cand.set_type:
        s += 500
    s += 10 * len(tokens(item.name) & tokens(cand.variant_key))
    if cand.is_base_row:
        s += 1                          # tie-break: แถวฐานชนะแถวแตกย่อย
    return s

def pick(item, candidates):
    pool = [c for c in candidates if not hard_reject(item, c)]
    if not pool:
        return None                     # ตัดหมด = ยอมว่าง ดีกว่าเดาผิด
    return max(pool, key=lambda c: (soft_score(item, c), -c.id))

Layer two: send one dataset and compute in the browser

With the summary table in place, fetching data for the whole page really did take just one query. I built a single endpoint that ran one set of joins and returned one JSON payload. No HTML, no computation.

// dataset_api.php — ส่งข้อมูลดิบทั้งก้อนของ session หนึ่ง ไม่คำนวณอะไรเลย
header('Content-Type: application/json; charset=utf-8');
header('Cache-Control: private, max-age=1800');   // 30 นาที ให้ตรงกับ TTL ฝั่ง client

$rows = $db->query("
    SELECT i.code, i.name, i.weight_json, i.set_type, i.pattern_no,
           m.year, m.quota, m.applied, m.passed, m.min_score, m.max_score
    FROM   items i
    LEFT JOIN item_history_matched m ON m.item_code = i.code
    WHERE  i.active = 1
    ORDER BY i.code, m.year")->fetchAll(PDO::FETCH_ASSOC);

$out = ['ts' => time(), 'items' => group_by_code($rows), 'subjects' => subject_map()];
echo json_encode($out, JSON_UNESCAPED_UNICODE);   // ไม่ escape ไทย ประหยัดขนาดเยอะ

The payload came to around 1.3 MB uncompressed. That sounds large, but enabling gzip on the web server brought it down to around 107 KB, because JSON with repeated keys compresses very well. Measured response time was around 185 milliseconds. From there, all the formulas moved into the browser.

// assets/matcher.js — สูตรตัวเดียวกับ PHP แต่รันในเบราว์เซอร์
export function bufferOf(item, profile) {
  const w = item.weight;                       // น้ำหนักถ่วงต่อหมวด
  let sum = 0, div = 0;
  for (const k of Object.keys(w)) {
    sum += (profile[k] ?? 0) * w[k];
    div += w[k];
  }
  return div === 0 ? null : sum / div;
}

export function matchItems(items, profile, filter) {
  const out = [];
  for (const it of items) {                    // 1,090 รอบ ในหน่วยความจำล้วน
    if (filter.group && it.group !== filter.group) continue;
    const b = bufferOf(it, profile);
    if (b === null) continue;
    out.push({ ...it, buffer: b, chance: chanceOf(it, b) });
  }
  out.sort((a, b) => b.chance - a.chance);
  return out;
}

Looping through and computing all 1,090 items took 12 milliseconds, measured with performance.now() on an ordinary laptop, not a powerful machine. Rendering all 1,090 table rows into the DOM took less than another 200 milliseconds. Together, that meant changing a filter updated the results immediately, with no need for a spinner.

Transparency mattered to me just as much as speed. Once the formulas were in a file anyone could open, anyone wondering where a number came from could open DevTools and trace it line by line. They did not have to rely solely on the system developer's explanation.

Cache the dataset in localStorage for 30 minutes

If users closed the tab and reopened it, or navigated back, there was no reason to download 107 KB again. I cached it in localStorage with a timestamp.

// assets/app.js — โหลด dataset โดยใช้แคชถ้ายังไม่หมดอายุ
const TTL = 30 * 60 * 1000;                    // 30 นาที
const KEY = (sid) => `ds_cache_${sid}`;

async function loadDataset(sid) {
  try {
    const hit = JSON.parse(localStorage.getItem(KEY(sid)) || 'null');
    if (hit && Date.now() - hit.savedAt < TTL) return hit.data;
  } catch (e) { /* JSON เสียหรือ quota เต็ม ให้ตกไปโหลดใหม่ */ }

  const res = await fetch(`dataset_api.php?sid=${encodeURIComponent(sid)}`);
  const data = await res.json();
  try {
    localStorage.setItem(KEY(sid), JSON.stringify({ savedAt: Date.now(), data }));
  } catch (e) {
    localStorage.removeItem(KEY(sid));          // เต็มก็ล้างทิ้ง อย่าให้หน้าพัง
  }
  return data;
}

I chose a TTL of 30 minutes based on how the data actually behaves. The summary table is rebuilt once a day. A shorter TTL would needlessly reduce the benefit of caching, while a full day would risk users seeing stale data after a rebuild. Anyone adopting this can adjust it to their own data update cycle.

parity test: both formula implementations must match on every row

This is the part I consider the most important in the entire job. When I moved the formulas to JS, I did not delete includes/matcher.php. I kept it as the source of truth and wrote a test requiring both implementations to produce matching results on every row. The real risk in this architecture is not speed. It is the day someone changes the formulas on one side while the other stays on the old version.

// tests/dump_php_result.php — ให้ฝั่ง PHP คายผลเต็มความละเอียดออกมาเป็น JSON
$items = load_items();
$prof  = json_decode(file_get_contents('tests/fixtures/profile.json'), true);
$rows  = [];
foreach (match_programs($items, $prof) as $r) {
    $rows[] = [
        'code'   => $r['code'],
        'buffer' => sprintf('%.17g', $r['buffer']),   // บังคับ 17 หลัก อย่าให้ปัดโชว์
        'chance' => sprintf('%.17g', $r['chance']),
        'year_used' => $r['year_used'],
    ];
}
file_put_contents('tests/out/php.json', json_encode($rows, JSON_UNESCAPED_UNICODE));

// tests/parity.mjs — รันสูตรฝั่ง JS ด้วย fixture เดียวกัน แล้วเทียบทีละแถว
import { readFileSync } from 'node:fs';
import { matchItems } from '../assets/matcher.js';

const EPS   = 1e-12;
const items = JSON.parse(readFileSync('tests/fixtures/dataset.json', 'utf8')).items;
const prof  = JSON.parse(readFileSync('tests/fixtures/profile.json', 'utf8'));
const php   = new Map(JSON.parse(readFileSync('tests/out/php.json', 'utf8')).map(r => [r.code, r]));
const js    = matchItems(items, prof, {});

let bad = 0;
if (js.length !== php.size) {
  console.error(`[ERR] จำนวนแถวไม่ตรง: js=${js.length} php=${php.size}`);
  bad++;
}
for (const r of js) {
  const p = php.get(r.code);
  if (!p) { console.error(`[ERR] ${r.code}: ฝั่ง PHP ไม่มีแถวนี้`); bad++; continue; }
  for (const f of ['buffer', 'chance']) {
    const a = Number(p[f]), b = r[f];
    // ต่างกันเกิน EPS = สูตรหลุดกัน ไม่ใช่เรื่องปัดเศษ
    if (!(Math.abs(a - b) <= EPS * Math.max(1, Math.abs(a)))) {
      console.error(`[ERR] ${r.code}.${f}: php=${a} js=${b} diff=${Math.abs(a - b)}`);
      if (++bad >= 20) { console.error('...ตัดที่ 20 แถวแรก'); break; }
    }
  }
  if (String(p.year_used) !== String(r.year_used)) {
    console.error(`[ERR] ${r.code}.year_used: php=${p.year_used} js=${r.year_used}`); bad++;
  }
}
console.log(bad === 0
  ? `[OK] ตรงกันทั้ง ${js.length} แถว`
  : `[FAIL] ไม่ตรง ${bad} จุด`);
process.exit(bad === 0 ? 0 : 1);

The actual run returned [OK] ตรงกันทั้ง 1,090 แถว, with PHP taking 12 seconds and JS taking 12 milliseconds on the same fixture. I wired this test into a pre-commit hook.

# .git/hooks/pre-commit — กัน commit ที่แก้สูตรฝั่งเดียว
changed=$(git diff --cached --name-only)
echo "$changed" | grep -qE 'assets/matcher\.js|includes/matcher\.php' || exit 0
php tests/dump_php_result.php && node tests/parity.mjs || {
  echo "[BLOCK] parity test ไม่ผ่าน — สูตร PHP กับ JS ไม่ตรงกัน"; exit 1; }

These are the steps I put at the top of both files:

1. Always change the formulas in includes/matcher.php first, because it is the source of truth.
2. Update assets/matcher.js to match, including calculation order and rounding.
3. Run php tests/dump_php_result.php to output the expected results at full precision.
4. Run node tests/parity.mjs; every row must return OK before committing.
5. If the matcher changes, rebuild the summary table too, then run parity again.

Before and after, in numbers

I want to stress that this approach does not suit every job. Three conditions make it worthwhile. First, the dataset must have a known upper bound: thousands of rows, not millions. Second, the entire dataset sent to the browser must already be data that the user is authorized to see. If access needs to be filtered row by row, it must not be sent down as a whole. Third, the formulas must be stable enough to maintain two implementations. If they change every week, do not do this. Keep them on one side and fix the queries instead.

My one brief lesson from this job is that before looking for a slow query, I should count how many queries are being run. In many jobs I have seen, the slowness does not come from a single statement. It comes from putting the formulas in the wrong layer in the first place. Anyone adapting this structure can set the dataset size and TTL to suit their own work. If I have left anything out of the explanation or made a typo, I will come back and correct it later.