pagination performance on large datasets

[boicetomlin]

On large datasets using SQL_CALC_FOUND_ROWS produces slow results. it would be nice to have a way to select a page of results and issue a separate query to get the number of found rows. ordering, joining in combination with SQL_CALC_FOUND_ROWS produces even slower results. I'm seeing much faster performance by issuing separate queries for the limited result set and then another for the count result. however the class does not appear to offer this ability out of the box. the get() method only allows me to limit the results not select a limit range and the paginate method uses SQL_CALC_FOUND_ROWS without an option to turn it off. I would love to work on a modification to this class that offered a few different methods of pagination that addressed performance issues on large datasets.

[syrian-dev]

i have same issue
pagination so slow with 700k records
do you find a solution ?

[boicetomlin]

my solution was to first disable SQL_CALC_FOUND_ROWS. then do a query to select the data and another query with the same conditions to select the count.