Resource exhaustion during deep pagination in Zend\Paginator\Adapter\DbSelect
0 reputation · 12 May 2024, 23:49 UTC
Pagination Performance on Large Datasets
When utilizing Zend\Paginator\Adapter\DbSelect to handle large datasets, the framework relies on Zend\Db\Sql\Select to apply LIMIT and OFFSET clauses. As the page number increases, the database must scan and discard an increasing number of rows to reach the requested offset, leading to significant performance degradation.
Query Bounding and Resource Constraints
The Zend\Paginator\Paginator component allows the items-per-page value to be set via setItemCountPerPage(). However, the framework does not enforce a maximum limit on this value, nor does it provide a native keyset pagination (cursor-based) alternative to avoid the overhead of deep offsets.
Given these architectural constraints in Zend Framework 3, how should the application layer bound these queries to prevent database resource exhaustion? Is there a documented method to override the default COUNT query behavior to reduce the cost of calculating total pages on massive tables?
zend