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.
Pain Point #1. Querying Stock Balance: The Failure of SUM() and the Physics of the LIMIT Hack
