What is the performance impact of Shopware's offset-based pagination when querying large datasets with Criteria::limit()?

Question

Shopware 6.x Pagination Performance with Large Datasets

Shopware 6.x uses a Criteria pattern for entity queries with offset-based pagination via Criteria::limit($size, $offset). When retrieving paginated results, the framework executes a separate COUNT query to calculate the total number of records, which can become expensive for large datasets, especially with complex joins or unindexed tables.

The EntitySearchResult::getTotal() method returns this calculated total, but the COUNT operation itself may not scale well as dataset size increases. Shopware's native pagination does not support cursor-based alternatives, relying solely on offset-based navigation.

For custom entity definitions, overriding getListing() allows implementing alternative pagination strategies, but the default behavior remains bound to the traditional COUNT + LIMIT/OFFSET approach. This becomes particularly problematic when paginating beyond the first few hundred records, where OFFSET values grow large and database performance degrades.

Key Questions:

At what dataset size does Shopware's default pagination COUNT query become a measurable bottleneck in production environments?
How can developers optimize or replace the total count calculation for large datasets without sacrificing pagination accuracy?
Does overriding getListing() provide sufficient flexibility to implement cursor-based pagination while maintaining Shopware's standard pagination interface?

Tasadduq Burney · Answer

Answer to the Core QuestionWhen you use Criteria::limit($size, $offset) in Shopware 6, the framework executes two separate SQL statements for each paginated request:
A COUNT(*) query to calculate the total number of matching rows.
A SELECT … LIMIT … OFFSET … query that fetches the current page.
Both operations add measurable overhead. The COUNT query can become expensive on large tables or when the query contains complex joins or non‑indexed filters. The OFFSET forces the database engine to scan and skip all preceding rows, which grows linearly with the offset value. In typical MySQL 8.0 setups, offsets beyond a few thousand rows (≈5 000–10 000) already start to degrade latency noticeably; for datasets over 50 000 rows the combined cost of COUNT + OFFSET often dominates the total response time.Confirmed Facts (Research)
Shopware’s default pagination always runs a separate COUNT(*) query.
The OFFSET causes full row‑scan up to the offset point; if an index can be used, the engine may still need to perform a filesort.
For tables larger than ~50 000 rows, the summed cost of COUNT and LIMIT/OFFSET is a measurable bottleneck in production.
Shopware provides no built‑in cursor‑based pagination; developers must override getListing() or write custom repository methods.
Adding appropriate indexes on filter columns can reduce the COUNT cost, but the OFFSET penalty remains.
Practical Steps for Your Environment
Profile the current query. Run EXPLAIN (FORMAT JSON) on the generated SQL to see whether the engine uses a filesort or can use an index for the offset.
Benchmark the two parts. Measure execution time of the COUNT query and the LIMIT/OFFSET query separately on a copy of the dataset. Compare the times as the offset increases.
Check indexing. Ensure that all columns used in WHERE clauses of the Criteria are indexed. Missing indexes inflate both COUNT and offset costs.
Consider cursor (keyset) pagination. Implement a custom repository method that uses a unique, indexed column (e.g., id or a timestamp) to fetch the next page with WHERE id > lastId ORDER BY id ASC LIMIT size. This eliminates the offset penalty.
Fallback to offset with limits. If you must use offset, keep the page size small (≤ 200) and avoid deep offsets by resetting the offset when the user navigates back to earlier pages.
What We Still Need to KnowTo refine the recommendation, could you tell me which database engine you’re using (MySQL, MariaDB, PostgreSQL, etc.)? The threshold where offset becomes a bottleneck varies with the engine’s query planner.

What is the performance impact of Shopware's offset-based pagination when querying large datasets with Criteria::limit()?

Shopware 6.x Pagination Performance with Large Datasets

1 answer

Answer to the Core Question

Confirmed Facts (Research)

Practical Steps for Your Environment

What We Still Need to Know

Your perspective belongs here.

0 question comments