"Is one big query faster than many small ones?" is one of the most-viewed database questions there is, and the usual answer is "batch it". In July I batched the writes of my search engine's indexer, which cut the number of MySQL queries per indexed page roughly in half. It did not make the database server any less busy, and the design document I'd written beforehand predicted a much bigger win. The gap between the two is the useful part.
44 queries per page
The question came from a capacity problem. The pipeline server, the box that ran MySQL plus the crawlers and indexers, had a load average of 6.44 on 4 cores, and its MySQL container was at 96% of its memory limit. Adding more indexer processes would have made both worse.
Two metrics were already in the monitoring: MySQL's query rate and the number of documents indexed per second. Dividing one by the other gave about 475 queries a second against about 10.8 documents a second: roughly 44 MySQL queries for every page indexed. Tracing the indexer's code confirmed the same order of magnitude. Most of it was work done once per file that could be done once per batch of 50.
The codebase already had two examples of the fix. The search index got its documents in bulk, buffered in memory and flushed 50 at a time. And an earlier change had replaced what the code's own comment called "up to 75 round trips for a 25-document batch" with a fixed four. The plan was to apply the same "collect in memory, write once per batch" pattern to everything else the indexer did per file.
What the design predicted
The design document estimated that a 50-file batch going from roughly 750 to 1,100 round trips down to 10 to 15 consolidated statements would mean one to two orders of magnitude fewer round trips. It also carried a caveat, written before any code: this cuts the number of queries, which drives per-statement overhead like parsing, lock acquisition and network round trips, but not proportionally the CPU time MySQL spends, because the same rows get touched either way. It still expected "a large (not marginal) win".
What it delivered
The work shipped in stages over one day: caching values that were re-read for every file, buffering per-host statistics into one batched write, moving link extraction to a single per-batch flush, loading a whole batch's URL rows in one query instead of one per file, and caching two lookups that ran up to three times per file.
Then the same ratio was measured again, five samples, stable: about 22.5 queries per document, down from about 44. That's a 49% cut. The load on the server: 6.8, against 6.44 before. Indexing throughput: about 11.1 to 11.8 documents a second, against 10.8.
So the query count halved, the server was just as busy, and slightly more pages got indexed. The caveat had been the accurate part of the prediction: the freed capacity went into indexing a bit faster, not into a quieter machine. The real constraint was CPU work that fewer statements didn't remove.
The stage that wasn't built
One stage remained: batching the most complicated lookups across files, estimated to save another two or three round trips per file. It was also the riskiest part of the design. While planning an earlier stage, I'd found that batching reads ahead of time can reintroduce duplicate-detection gaps that the per-file version avoided only because it wrote synchronously. With the measurement pointing at CPU rather than round trips, that last stage was evaluated and deliberately not built. The note says to profile where the CPU time goes before assuming it's the right lever, if load ever becomes a problem again.
A MySQL detail from the batched upserts
One of the batched writes used MySQL 8's row-alias syntax for upserts, which replaces the deprecated VALUES() function in ON DUPLICATE KEY UPDATE. The first version failed with:
ERROR 1052 (23000): Column 'count' in field list is ambiguous
The statement was ... AS new_vals ON DUPLICATE KEY UPDATE count = count + new_vals.count, and with a row alias in play, MySQL wants the target table's column qualified as well: host_language_count.count = host_language_count.count + new_vals.count. I reproduced both versions on MySQL 8.0.46. It was caught by running the query against a real table before trusting the design's SQL.
So: one big query or many small ones?
Fewer, bigger statements do cut per-statement overhead, and when that overhead is your bottleneck, batching is the right fix. Whether it is your bottleneck is a measurement, not an assumption. The number that settled it here was a simple ratio, queries per document, measured before and after, next to the load average. Without the "after", the batching would have looked like a success on query count alone.
Leave a comment
Comments are reviewed before they appear.