Pagination, how to disable COUNT query?

Hi,
Is it possible in Pagination component to disable SELECT COUNT(1) - query ?
For big tables on InnoDB it takes long time.

Thank you.

How do you expect to get the total count (which is required to generate page numbers) without doing the count query?

1 Like

Easy, not use Totatal count at all.

Thank you.

Have you indexes table ?

Sure.
You can make a test:
Take a table with 1000k rows on InnoDB and paginate it.
COUNT (1) FROM - works few seconds.

I encountered a similar issue while paging results for data views to sync data with devices. The $query->count() function in paginator was taking 500ms to do the count so was a big performance hit over hundreds of pages.

My solution was to roll my own pagination, passing in values for page/limit into a finder. I can then use another finder to retrieve the count - and this time cache the results where ever needed.

This resulted in a huge performance boost and removed 90% of the load on the server as well.

Easy, not use Totatal count at all.

The core paginator cannot work without a total count, so you will have to roll your own.

I understand it.
Thank you.
@ADmad Interesting , How do you use Paginator on your “big tables” ? Or maybe, What is max roes cont when you use Paginator?

Seems to me that if you’ve got a table with even thousands of rows, pagination is often not the best approach, and should be replaced with things like searches and autocomplete.