Stop building running totals in PHP

Two hundred thousand rows over the wire to compute forty numbers. That was our monthly report on MySQL 5.7: fetch all transactions for the period, loop in PHP, accumulate a running balance, compare each row with the previous one, rank customers by volume. Classic 5.7 shape, because the database could not say “previous row” or “rank within group”.

We are finally moving that project to MySQL 8. Two years after GA, which by database standards is reckless haste. The first win had nothing to do with performance. We deleted PHP.

MySQL 8 has window functions, and the whole loop collapses into SQL:

SELECT customer_id,
       amount,
       SUM(amount) OVER (PARTITION BY customer_id
                         ORDER BY created_at, id) AS balance,
       amount - LAG(amount) OVER (PARTITION BY customer_id
                                  ORDER BY created_at, id) AS delta,
       ROW_NUMBER() OVER (ORDER BY amount DESC) AS rnk
FROM transactions
WHERE created_at >= :from AND created_at < :to;

The database was reading those rows anyway. Now it also folds them and sends back only what the report shows. Rows over the wire: two hundred thousand became a few hundred. The PHP file with the accumulator loop and its three subtle off-by-one bugs is gone. That file had unit tests. The SQL needs one integration test.

Check the plan though. Window functions are not free. Each distinct OVER (...) ordering may cost a sort or a filesort. An index on the partition and order columns, (customer_id, created_at, id) here, keeps it honest. EXPLAIN before celebrating.

The principle is old, from before ORMs taught us to fear SQL. Move computation to the data, not data to the computation. Window functions just made it affordable on MySQL. PostgreSQL people have been doing this for a decade and are allowed one smug nod.

I wrote the original loop. Two of the three off-by-one bugs were mine.