The cohort report ran at two in the morning, took eleven minutes, needed 380 megabytes and produced a CSV nobody could regenerate on demand. It had been written in 2016 against MySQL 5.6, where the only way to compute a running total was to fetch every row and add them up in PHP — and MySQL had grown window functions two years earlier without anybody revisiting it.
The symptom
$rows = $db->query(
'SELECT customer_id, cohort_month, placed_at, total_cents
FROM orders ORDER BY customer_id, placed_at'
)->fetchAll(PDO::FETCH_ASSOC);
$running = $sequence = $out = [];
foreach ($rows as $row) {
$c = $row['customer_id'];
$running[$c] = ($running[$c] ?? 0) + $row['total_cents'];
$sequence[$c] = ($sequence[$c] ?? 0) + 1;
$out[] = $row + [
'lifetime_cents' => $running[$c],
'order_number' => $sequence[$c],
];
}$ /usr/bin/time -v php artisan report:cohorts 2>&1 | grep -E 'resident|Elapsed'
Elapsed (wall clock) time: 11:04.21
Maximum resident set size (kbytes): 389204
$ mysql -Nse 'SELECT COUNT(*) FROM orders'
1204118One point two million rows fetched into PHP, an output array of the same size built alongside, and eleven minutes of a machine adding numbers. Nothing about it is wrong for 2016; all of it is unnecessary now.
Why it happens
A running total and a per-customer sequence number both require knowing about the rows before the current one, and before window functions SQL had no way to express that without a correlated subquery per row. Fetching everything and looping was genuinely the fastest correct answer.
It outlived the constraint because nothing prompts a revisit. The report worked, it ran at two in the morning where its eleven minutes were invisible, and the database upgrade that made the alternative possible was not accompanied by an audit of what the upgrade unblocked.
The fix
The same report, section by section
SELECT customer_id, cohort_month, placed_at, total_cents,
SUM(total_cents) OVER (
PARTITION BY customer_id ORDER BY placed_at, id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS lifetime_cents,
ROW_NUMBER() OVER (
PARTITION BY customer_id ORDER BY placed_at, id
) AS order_number
FROM orders ORDER BY customer_id, placed_at, id;The two accumulators in the PHP loop become two window expressions, and the loop disappears entirely. The id in both orderings is the tiebreaker: two orders placed in the same second are common, and without it both the running total and the sequence number are non-deterministic between runs.
That non-determinism existed in the PHP version too, inherited from the query’s ORDER BY, and had produced a report that differed between runs on unchanged data — which had been investigated twice as a caching problem.
The frame clause, which changes what the aggregate means
-- a running total: everything up to this row
SUM(x) OVER (PARTITION BY customer_id ORDER BY placed_at
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
-- the customer's total: every row in the partition
SUM(x) OVER (PARTITION BY customer_id)
-- a trailing three-order average
AVG(x) OVER (PARTITION BY customer_id ORDER BY placed_at
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
-- ROWS counts rows. RANGE counts VALUES, so with ties it
-- includes every peer of the current row. these differ.Adding ORDER BY to a window changes what the aggregate means, which is not what ordering does anywhere else in SQL — the default frame becomes everything from the start of the partition to the current row, so a SUM that looks like a total is a running total. Writing the frame out explicitly is three extra words and removes the whole class of misunderstanding.
The ROWS versus RANGE distinction is the one that produces a wrong report rather than an obviously broken one: with two orders in the same second, a RANGE frame includes both in each one’s running total, so both rows show the same figure and it is larger than either should be.
The index the window needs, which is not the one the WHERE needed
-- the index that served the original query's ORDER BY:
-- KEY idx_customer_placed (customer_id, placed_at)
mysql> EXPLAIN SELECT ... window functions ... ;
| table | key | rows | Extra |
| orders | idx_customer_placed | 1204118 | Using temporary |
-- the temporary table IS the window buffering. adding id:
ALTER TABLE orders ADD INDEX idx_win
(customer_id, placed_at, id, total_cents);
| orders | idx_win | 1204118 | Using index |A window function needs its partition and ordering columns available in order, and an index providing exactly that lets the server stream rather than sort into a temporary table. Adding total_cents makes it covering, so the query never touches the table at all — which on 1.2 million rows is the difference between six seconds and ninety.
This is the step that gets skipped, because the query is already dramatically faster than the PHP loop and nobody looks further. Using temporary in the plan is the signal, and it is worth checking on any query with a window in it.
The parts that stay in PHP
// streaming the result rather than fetching it
$stmt = $db->prepare($sql);
$stmt->execute();
$out = fopen($path, 'w');
fputcsv($out, ['customer_id', 'cohort', 'placed_at', 'total', 'lifetime', 'n']);
while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
fputcsv($out, $row);
}
fclose($out);Fetching row by row rather than fetchAll is what takes the memory from 380 megabytes to a constant, and it only becomes possible once PHP has no accumulator to maintain. With PDO and MySQL the buffered mode still holds the whole result in the client library, so PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false is required for this to be genuinely streaming.
The unbuffered mode has a real constraint: no other query can run on that connection until the result is fully consumed. For an export loop that is fine and for anything doing lookups mid-iteration it is a fatal error, which is worth knowing before switching it on globally.
Verifying it worked
$ /usr/bin/time -v php artisan report:cohorts 2>&1 | grep -E 'resident|Elapsed'
Elapsed (wall clock) time: 0:14.82
Maximum resident set size (kbytes): 41208
# 11m04s → 14.8s. 380 MB → 41 MB.
# and the assertion that matters more than either
$ diff <(sort old-2020-07.csv) <(sort new-2020-07.csv); echo $?
0Diffing the output of both implementations against a month both could handle is the assertion that matters, and sorting first removes any ordering difference — which is legitimate and is not something anybody wants to debug at the same time as verifying the arithmetic.
The first attempt did not diff clean: the running totals differed for 41 customers, all of whom had two orders in the same second. The PHP version had been ordering by placed_at alone and processing them in whatever order MySQL returned, so the old report had been arbitrary for those customers all along. The new one is deterministic and differs from the old, which is the correct outcome and needed explaining to the person who read the report.
# and the change that was the actual point
$ curl -s -o /dev/null -w '%{time_total}n'
'https://admin.internal/reports/cohorts?month=2020-07'
0.412 # the report is a page now, not a nightly file.What this costs
SQL that most of the team cannot modify. A forty-line query with three window functions and an explicit frame clause is a different skill from the PHP loop it replaced, and the person who wrote it becomes the person who changes it. That is a real concentration of knowledge and the mitigation is a comment above each window explaining what it computes in words — which sounds obvious and is routinely omitted.
The other cost is the index, which nothing enforces. Dropping idx_win during a cleanup turns a fifteen-second report back into a ninety-second one with no error and no obvious cause, and the index looks unused to performance_schema if the report has not run during the sampling window. Naming indexes after what they serve and keeping a comment in both directions is the best available defence, and it is weaker than a foreign key.