When PHP's array_multisort Silently Reordered My Invoice Dates

We had a reporting job on PHP 8.1.27, Debian 12, 8 vCPU worker, pulling about 120k invoice rows nightly from MySQL 8.0 and writing a CSV per customer. Each customer’s rows came back already ordered by invoice_date ASC, and the job needed them grouped by currency, then by date, then by amount. This felt trivial, so I did what I always did: one usort with a custom comparator. That was the bug.

On a specific customer with ~47k rows, the output CSV had dates jumping back and forth: 2024-03-02, 2024-02-28, 2024-03-01. Finance noticed. I initially blamed the SQL, then PDO, then the timezone setting in php.ini (date.timezone=UTC, by the way, and the data was UTC too).

The comparator looked like this:

usort($rows, function (array $a, array $b) {
    return [$a['currency'], $a['invoice_date'], $a['amount']]
        <=> [$b['currency'], $b['invoice_date'], $b['amount']];
});

Spread operator on arrays inside usort is fine, and <=> on arrays is fine too. The real problem was amount. It came from PDO as a string, because we used PDO::ATTR_EMULATE_PREPARES (long story, another post). So "1000.00" compared less than "900.00", and "10.00" greater than "9.00". The sort was stable in PHP 8, which made it worse: rows kept their relative order, so the bad outcome looked plausibly ordered until someone read the dates.

What actually fixed it was not the comparator. It was casting at the boundary:

foreach ($rows as &$r) {
    $r['amount'] = (int) round(((float) $r['amount']) * 100);
}
unset($r);

I also moved to array_multisort, which I should have used from the start for multi-key sorting on 100k+ rows:

$currency = array_column($rows, 'currency');
$date     = array_column($rows, 'invoice_date');
$cents    = array_column($rows, 'amount'); // already int cents
array_multisort($currency, SORT_ASC, $date, SORT_ASC, $cents, SORT_ASC, $rows);

Mechanically, array_multisort reindexes and sorts in one pass, avoiding the userland comparator called O(n log n) times with array allocations each call. On 120k rows, the nightly job went from ~9.4s to ~1.2s for the sort step. The tradeoff: array_multisort needs column arrays in memory, so peak memory rose by roughly 3MB per 10k rows for these short strings. On our 512MB worker that was fine, but on a smaller container it wouldn’t be.

Where this fails: array_multisort sorts strings with SORT_STRING by default, which is fine for ISO dates but wrong for d/m/Y or locale-aware names. If two keys need different semantics (numeric cents vs. lowercase currency), you must pre-normalize both before the call; array_multisort will not apply a custom comparator per column. I also learned not to trust <=> with mixed types — always cast at the boundary where PDO hands you strings. And if you unset($r) after a by-reference foreach, remember the reference survives until you unset it; I lost an hour to a mutated last row in a different script.

The one place I’m still unsure: whether pushing the sort into MySQL (ORDER BY currency, invoice_date, amount+0) would be cheaper than doing it in PHP on this data size. It would avoid the PHP memory spike, but adding +0 on a column changes index usage and I haven’t measured it on this query shape. I suspect for 120k rows the DB is better, but I want numbers before I commit.

Has anyone benchmarked ORDER BY on a cast expression against PHP-side array_multisort for this row count? I’m curious where the crossover sits on your workloads.