Pagination, how to disable COUNT query?


#1

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

Thank you.


#2

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


#3

Easy, not use Totatal count at all.

Thank you.


#4

Have you indexes table ?


#5

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


#6

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.


#7

Easy, not use Totatal count at all.

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


#8

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?


#9

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.