If you have ever designed inventory accounting, an ERP system, or billing, you know: calculating stock balances is a minefield.
In SQL textbooks, everything looks clean: run SUM(quantity)
over transaction history or use a ROW_NUMBER()
window function — done. But as soon as the system grows to millions of rows, these "pretty" academic solutions turn the RDBMS into a sluggish bottleneck. And when real business scenarios appear (negative sales, backdated documents, on-the-fly manufacturing), cost price quietly explodes exponentially.
What's worse, modern AI assistants and incoming junior "optimizers" think strictly in terms of these textbooks. They see low-level RDBMS hacks in the code, panic, make the code "clean"... and a week later, the accounting department receives garbage data.
In this article, I'll break down 4 fundamental pain points of stock registers, explain the query optimizer physics of MySQL/MariaDB, and demonstrate the Snapshot Log architecture we arrived at after 12 years of developing high-load systems.
SUM()
and the Physics of the LIMIT Hack
SUM()
and Window Functions Kill the Database
Calculating stock balance via SUM(quantity)
across the entire transaction history on multi-million row datasets is a classic architectural mistake. $O(N)$ complexity on every single check turns any report into an hour-long wait.
The second attempt by "academics" is using window functions:
-- Pseudo-pretty textbook SQL
SELECT item_id, warehouse_id, remains_quantity
FROM (
SELECT item_id, warehouse_id, remains_quantity,
ROW_NUMBER() OVER (PARTITION BY warehouse_id, item_id ORDER BY dt DESC, id DESC) as rn
FROM stock_register
WHERE dt <= '2026-07-01'
) t WHERE rn = 1;
The Physics of Disaster: In MySQL/MariaDB, this query on large volumes leads to a fatal filesort
. The engine is forced to create a temporary table on disk, scan gigabytes of indexes, and sort them in a temp file. Performance drops by orders of magnitude.
Instead of a simple movement log, we store a state snapshot (Snapshot Log). Every document execution increments two numbers:
remains_quantity(new) = remains_quantity(old) + Δqty
Calculation complexity is O(1). The current balance is always the last recorded row.
LIMIT
Hack and the AI Dead End
To fetch the last row without window functions, subquery grouping is used. But RDBMS query optimizers are "smart": if you write a standard subquery with ORDER BY
, the optimizer simply discards the sorting, and GROUP BY
returns random rows.
To force the engine to actually sort the data, forced subquery materialization is applied:
SELECT
r.warehouse_id,
r.item_id,
IFNULL(remains_quantity, 0) AS remains,
IFNULL(remains_costsum, 0) AS sumremains
FROM (
SELECT warehouse_id, item_id, remains_quantity, remains_costsum
FROM stock_register
WHERE dt <= :target_dt
AND warehouse_id IN (:warehouses)
AND item_id IN (:items)
/* Subquery materialization + sorting triad */
ORDER BY dt DESC, is_overplus_neg ASC, id DESC
LIMIT 9999999999
) AS r
GROUP BY warehouse_id, item_id;
Production Drama:The same story repeats regularly. A new developer (or AI assistant) arrives, sees the wildLIMIT 9999999999
, and thinks:"What a hack! Let me make this clean and remove the LIMIT". The query suddenly flies, everyone is happy, and a day later stock balances break because MariaDB grouped random rows.
In an ideal world, negative inventory doesn't exist. In real business — a cashier makes a mistake, an invoice wasn't posted in time, physical goods were dispatched, but they aren't in the database yet.
If negative balances are allowed, a mathematical anomaly occurs. If item balance goes down to -5
units at zero cost, and then a batch arrives at $100, basic school math produces a wild distortion in cost price.
is_overplus
):is_overplus = 1
) that keeps the balance at zero.remains_costsum
is lastprice
)remains_costsum = remains_quantity * lastprice
In food service or retail, nobody creates separate "Production" documents before selling every burger or cup of coffee. Items are sold "on the fly".
At that exact moment, a transit calculation occurs within a single document:
parentid
) are written off the warehouse, establishing cost price.id
) is received for the total cost of ingredients and remains_quantity
for the dish itself doesn't change (stays 0), but financial accounting records the exact cost price expense without distorting the warehouse average price.When a user enters a document dated last Tuesday, the entire chain of calculated stock balances (remains_quantity
, remains_costsum
) after that date becomes invalid.
Recalculating 5 years of history is suicide for the database.
To solve this, the "Point of No Return" ( $mindt) algorithm is used. We find the date of the last guaranteed positive receiving transaction for the items:
SELECT warehouse_id, item_id, MAX(dt) AS mindt
FROM stock_register
WHERE dt <= :target_dt
AND is_overplus = 0
AND quantity > 0
GROUP BY warehouse_id, item_id;
All calculations and recalculation cascades are cut off by the condition dt >= $mindt
, reducing scanned data volume by 90–95%.
Inventory accounting isn't about "pretty code." It's about understanding the query physics of a specific RDBMS, managing transactional integrity, and handling edge-case mathematics.
Explaining these rules every time to new developers or arguing with LLM models that constantly try to drop LIMIT 9999999999
or rewrite queries with ROW_NUMBER()
gets exhausting fast.
That's why I packed this entire 12-year architecture, sorting triad, is_overplus
logic, and MariaDB hacks into a specialized system prompt (AI Trainer).
If you use AI assistants to generate SQL and design backend architecture, you can simply plug this system prompt into context. After that, any AI starts generating verification queries, DDL, and recalculation functions strictly according to senior engineering standards, without rookie mistakes.