Two classics of the „shop got big, code got slow“ genre:

1. A listing endpoint that spends more time in COUNT(*) than in the actual query.
2. An export command that starts fast and gets slower with every page, until page 4,000 takes ten seconds.

Both have quiet solutions in the DAL. One is a Criteria mode with a misleading docblock, the other is an optimization you get for free — but only if you use the right method.

Part 1: Three ways to count (and one to avoid)

Every Criteria has a total count mode. Most developers never touch it, which means they get whatever the route defaults to. The three options (Search/Criteria.php:32-42):

TOTAL_COUNT_MODE_NONE — no count. Total is just the number of rows fetched. Free.

TOTAL_COUNT_MODE_EXACT — the expensive one. And it’s worth knowing how expensive: the DAL takes your entire search query — joins, filters, everything — strips ORDER BY and LIMIT, wraps it in a subquery and runs SELECT COUNT(*) over it (Dbal/EntitySearcher.php:160-176). On a filtered listing over a large product table you pay for your query twice.

TOTAL_COUNT_MODE_NEXT_PAGES — the clever middle ground:

$criteria->setLimit(24);
$criteria->setTotalCountMode(Criteria::TOTAL_COUNT_MODE_NEXT_PAGES);

No second query. Instead, the searcher over-fetches (EntitySearcher.php:148-155), returns your limit rows, and the total tells you how many more exist within the lookahead window. Enough to render „next page“ and a handful of page links — which is all an infinite scroll or a paginator with a page-window UI actually needs. Nobody clicks page 1,247 of 3,891 anyway; they search or filter.

Fun detail for the source-code archaeologists: the docblock on the constant says it fetches limit * 5 + 1 rows. The implementation says $criteria->getLimit() * 6 + 1 (EntitySearcher.php:154). The comment and the code disagree — the code wins, as usual. You get a six-page lookahead.

The product listing uses this mode out of the box, by the way. Your custom routes, filtered searches and API consumers probably use EXACT — and only sometimes because they need it.

Part 2: RepositoryIterator — keyset pagination behind your back

Now the export command. The naive loop pages with offsets:

$criteria->setOffset($page * 500); // page 4000: MySQL reads and throws away 2M rows

That’s why it gets slower with every page — OFFSET 2000000 means MySQL walks two million rows to discard them. The textbook fix is keyset pagination („WHERE id > last seen ORDER BY id“). The thing nobody tells you: Shopware’s RepositoryIterator does this automatically.

In the constructor (Dbal/Common/RepositoryIterator.php:48-52): if the entity has an auto-increment column, the iterator adds an autoIncrement sorting plus a range filter. After each batch it remembers the last auto-increment value and moves the range forward (fetchIds(), lines 90-97) — a rolling WHERE auto_increment > :last instead of a growing offset. Constant speed, whether you’re on row 500 or row 5,000,000.

$iterator = new RepositoryIterator($this->productRepository, $context, $criteria);

while (($ids = $iterator->fetchIds()) !== null) {
    // process 50 ids (or set your own limit on the criteria)
}

But — and this cost me a re-read of the class — the optimization only lives in fetchIds(). The sibling method fetch(), which returns full entities, pages with plain offsets (line 111: setOffset($offset + $limit)), auto-increment or not. So for big iterations: iterate IDs with fetchIds(), then load entities batch-wise by ID. Which is what you want anyway — loading 500 fat entities per page through a single criteria with associations is its own performance story.

Also nice: fetchIds() forces TOTAL_COUNT_MODE_NONE on every batch, so you don’t accidentally pay for a count per page. If you want a progress bar, call getTotal() once at the start — it runs one dedicated count query and leaves it at that.

TL;DR

For paginated UIs, Criteria::TOTAL_COUNT_MODE_NEXT_PAGES skips the expensive wrap-around COUNT(*) and over-fetches six pages ahead instead. For batch jobs, RepositoryIterator::fetchIds() silently switches to keyset pagination on entities with an auto-increment column — constant speed at any depth — while fetch() does not, so iterate IDs, not entities. None of this is in the docs; all of it is in EntitySearcher.php and RepositoryIterator.php.

Next up: the Twig you didn’t know Shopware speaks — {% foreach %}, {% break %}, real type checks, and a json_encode that keeps your prices from losing their zeros.

Drop me an email if your export commands just got faster!

---

Found in Shopware 6.7.0.0: count modes in Framework/DataAbstractionLayer/Search/Criteria.php:32-42, over-fetch (limit * 6 + 1) in Dbal/EntitySearcher.php:148-155, subquery COUNT in EntitySearcher.php:160-176, keyset switch in Dbal/Common/RepositoryIterator.php:48-52,90-97, offset-based fetch() in RepositoryIterator.php:104-119.

Other articles from this category