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

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?